Showing posts with label Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;. Show all posts
Showing posts with label Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;. Show all posts

Monday, October 19, 2015

SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN

Demo Database

In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico


And a selection from the "Orders" table:
OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2

SQL LEFT JOIN Example

The following SQL statement will return all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN

Demo Database

In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
And a selection from the "Orders" table:
OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2

SQL LEFT JOIN Example

The following SQL statement will return all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
ORDER BY Customers.CustomerName; 

CustomerNameOrderID
Alfreds Futterkiste   
Ana Trujillo Emparedados y helados  10308 
Antonio Moreno Taquería  10365 
Around the Horn  10383 
Around the Horn  10355 
Berglunds snabbköp  10278 
Berglunds snabbköp  10280 
Berglunds snabbköp  10384 
Blauer See Delikatessen   
Blondel père et fils  10360 
Blondel père et fils  10297 
Blondel père et fils  10436 
Blondel père et fils  10265 
Bólido Comidas preparadas  10326 
Bon app'  10331 
Bon app'  10340 
Bon app'  10362 
Bottom-Dollar Marketse  10411 
Bottom-Dollar Marketse  10431 
Bottom-Dollar Marketse  10389 
Bottom-Dollar Marketse  10410 
B's Beverages  10289 
Cactus Comidas para llevar   
Centro comercial Moctezuma  10259