Left Join by Example in SQL Server

The Left join (or left outer join) is one of the most commonly used join statement in SQL Server. A join lets us combine results from two or more tables into a single result set. The left join includes all the results from the left table and includes only the matching rows from the right table. This article explores the left join in more detail with examples

Syntax

The join_condition defines the condition on which the tables are joined. The final result includes

  1. All the rows from the left table. i.e tableA
  2. Each row of the tableB is compared with the tableA. If the condition is evaluated to true, then the rows from the tableB are included in the result.  
  3. A NULL value is assigned to every column of table B that does not have a matching row based on the join condition.

The LEFT Join is the same as LEFT OUTER JOIN. The keyword OUTER is optional.

Left Join

This join is best explained with the following Venn diagram.

Left Join

The circles in the above diagram represent the two tables. Table A and Table B, which we would like to join using the left join. The intersection part in the above picture shows the data rows which satisfy the join condition.

The left join of the above tables returns

  • All the rows from the left table (i.e Table A )
  • The matching rows from the right table (Table B )

Left Join Example

Now, let us see how to use it in a real-life example.

Sample database

Consider the following tables from the table reservation system of a restaurant. We used the same database in our example on inner join.

CustomerType : Type of Customer like VIP / Regular etc
Customers List of customers with Customer Type.
Tables The list of tables available in the restaurant. CustomerID field indicates that the customer has reserved the table.
Orders : The orders placed by the customer
DiscVoucher The discounts slab based on the total value of the order

--Sample database--

Sample database structure for Join Query example
Sample database structure for Join Query example

--SQL Query--

Left Join Query

We would like to know the list of all customers, along with their reserved table No.

The result includes all the customers. But it returns the only the matching rows from the Tables.

Left join example query result
Result of the query

What if you change the order of the tables appearing in the query i.e. exchanges customers & tables as shown below

The results are different from the above. The result includes all the tables and only matching rows from the customers.

WHERE Condition

The WHERE clause will filter the rows after the JOIN has occurred

For Example in the following query, the join happens first. The Where filter is applied to the join result to get the final output. The result of the following query is similar to the inner join query.

Difference between inner join and left join

You can compare the result with an inner join query

Different between inner join & left join
Different between inner join & left join
  1. In an inner join, result includes only the matching rows.
  2. Order of the table is not important in the inner join
Inner JoinLeft join
Only the matching rows are returned.All rows from Left table
Matching rows
Order of tables in the query does not make a differenceThe order of tables makes the difference to the result.

Left Join Examples

Here are some of the examples of Left Join

Subquery in Left Join

Multiple Conditions

Join Operator other than equality

You can also have a logical operator other than equality in the join condition. The following query returns the discount to be given to the customer based on their spend.

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

In this tutorial, we learned about how left join works in SQL Server. We also

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top