{"id":257,"date":"2018-04-25T11:30:03","date_gmt":"2018-04-25T04:30:03","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=257"},"modified":"2024-02-25T22:11:12","modified_gmt":"2024-02-25T15:11:12","slug":"sql-server-left-join","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-left-join\/","title":{"rendered":"SQL Server Left Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL Server LEFT JOIN<\/code> clause and how to query data from multiple tables.<\/p>\n\n\n\n

Introduction to SQL Server LEFT JOIN clause #<\/a><\/h2>\n\n\n\n

The LEFT JOIN<\/code> is a clause of the SELECT statement. The LEFT JOIN<\/code> clause allows you to query data<\/a> from multiple tables.<\/p>\n\n\n\n

The LEFT JOIN<\/code> returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, NULL<\/code> are used.<\/p>\n\n\n\n

The following illustrates how to join two tables T1 and T2 using the LEFT JOIN<\/code> clause:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    T1\nLEFT<\/span> JOIN<\/span> T2 ON<\/span>\n    join_predicate;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, T1 and T2 are the left and right tables, respectively.<\/p>\n\n\n\n
For each row from the T1 table, the query compares it with all the rows from the T2 table. If a pair of rows causes the join predicate to evaluate to TRUE<\/code>, the column values from these rows will be combined to form a new row which is then included in the result set.<\/p>\n\n\n\n
If a row from the left table (T1) does not have any matching row from the T2 table, the query combines column values of the row from the left table with NULL<\/code> for each column value from the right table.<\/p>\n\n\n\n
In short, the LEFT JOIN<\/code> clause returns all rows from the left table (T1) and matching rows or NULL<\/code> values from the right table (T2).<\/p>\n\n\n\n
The following illustrates the LEFT JOIN<\/code> of two tables T1(1, 2, 3) and T2(A, B, C). The LEFT JOIN<\/code> will match rows from the T1 table with the rows from the T2 table using patterns:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this illustration, no row from the T2 table matches row 1 from the T1 table; therefore, NULL is used. Rows 2 and 3 from the T1 table match rows A and B from the T2 table, respectively.<\/p>\n\n\n\n
SQL Server LEFT JOIN example #<\/a><\/h2>\n\n\n\n
See the following products<\/code> and order_items<\/code> tables:<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
Each sales order item includes one product. The link between the order_items<\/code> and the products<\/code> tables is the product_id<\/code> column.<\/p>\n\n\n\n
The following statement uses the LEFT JOIN<\/code> clause to query data from the products<\/code> and order_items<\/code> tables:<\/p>\n\n\n
SELECT<\/span>\n    product_name,\n    order_id\nFROM<\/span>\n    production.products p\nLEFT<\/span> JOIN<\/span> sales.order_items o ON<\/span> o.product_id = p.product_id\nORDER<\/span> BY<\/span>\n    order_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
As you see clearly from the result set, a list of NULL<\/code> in the order_id<\/code> column indicates that the corresponding products have not been sold to any customer yet.<\/p>\n\n\n\n
It is possible to use the WHERE<\/a><\/code> clause to limit the result set. The following query returns the products that do not appear in any sales order:<\/p>\n\n\n
SELECT<\/span>\n    product_name,\n    order_id\nFROM<\/span>\n    production.products p\nLEFT<\/span> JOIN<\/span> sales.order_items o ON<\/span> o.product_id = p.product_id\nWHERE<\/span> order_id IS<\/span> NULL<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
As always, SQL Server processes the WHERE<\/code> clause after the LEFT JOIN<\/code> clause.<\/p>\n\n\n\n
The following example shows how to join three tables: production.products<\/code>, sales.orders<\/code>, and sales.order_items<\/code> using the LEFT JOIN<\/code> clauses:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
SELECT<\/span>\n    p.product_name,\n    o.order_id,\n    i.item_id,\n    o.order_date\nFROM<\/span>\n    production.products p\n\tLEFT<\/span> JOIN<\/span> sales.order_items i\n\t\tON<\/span> i.product_id = p.product_id\n\tLEFT<\/span> JOIN<\/span> sales.orders o\n\t\tON<\/span> o.order_id = i.order_id\nORDER<\/span> BY<\/span>\n    order_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server LEFT JOIN: conditions in ON vs. WHERE clause #<\/a><\/h2>\n\n\n\n
The following query finds the products that belong to order id 100:<\/p>\n\n\n
SELECT<\/span>\n    product_name,\n    order_id\nFROM<\/span>\n    production.products p\nLEFT<\/span> JOIN<\/span> sales.order_items o \n   ON<\/span> o.product_id = p.product_id\nWHERE<\/span> order_id = 100<\/span>\nORDER<\/span> BY<\/span>\n    order_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Let’s move the condition order_id = 100<\/code> to the ON<\/code> clause:<\/p>\n\n\n
SELECT<\/span>\n    p.product_id,\n    product_name,\n    order_id\nFROM<\/span>\n    production.products p\n    LEFT<\/span> JOIN<\/span> sales.order_items o \n         ON<\/span> o.product_id = p.product_id AND<\/span> \n            o.order_id = 100<\/span>\nORDER<\/span> BY<\/span>\n    order_id DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The query returned all products, but only the order with id 100 has the associated product’s information.<\/p>\n\n\n\n
Note that for the INNER JOIN<\/a><\/code> clause, the condition in the ON<\/code> clause is functionally equivalent if it is placed in the WHERE<\/code> clause.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server LEFT JOIN<\/code> clause to retrieve data from multiple related tables.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t