To LEFT OUTER JOIN two tables, use the following syntax: 

SELECT column(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column
WHERE conditions 
ORDER BY column(s); 

The preceding syntax shows you how to join two tables together with a LEFT OUTER JOIN. You join a column in table1 that matches a column in table2. The WHERE and ORDER BY clauses are optional. They are there to show you that the LEFT OUTER JOIN syntax goes between the FROM and WHERE clauses. 

The following example will help you to understand how to use the LEFT OUTER JOIN: 

SELECT p.playerid, birthyear, schoolid, yearid 
FROM lahmansbaseballdb.people p 
LEFT OUTER JOIN lahmansbaseballdb.collegeplaying c 
ON p.playerid = c.playerid 
WHERE birthyear = 1985;

You can see in the preceding query that you are joining the people table to the collegeplaying table with a LEFT OUTER JOIN. This will return all rows from the people table and only corresponding rows from the collegeplaying table when a row in the people table has a birthyear of 1985. In the following screenshot, you will see the results of the preceding query:

As you can see in the preceding screenshot, the results show NULL for the rows in the people table that don't have a corresponding row in the collegeplaying table, and the rows that do match between people and collegeplaying have a complete row of data in the results. 

You can also leave off OUTER in the join, and it will run the same as if it were there. For example, the following code snippet will run a LEFT OUTER JOIN: 

LEFT OUTER JOIN lahmansbaseballdb.collegeplaying c 

This code snippet will work the same way and return the same results as the preceding code snippet: 

LEFT JOIN lahmansbaseballdb.collegeplaying c 

You can use either LEFT JOIN or LEFT OUTER JOIN with the same results.

As with INNER JOIN, you can join up to 61 tables in your query. You can LEFT OUTER JOIN three tables, as shown in the following SQL query: 

SELECT p.playerid, birthyear, schoolid, asf.yearid, gameid
FROM lahmansbaseballdb.people p 
LEFT OUTER JOIN lahmansbaseballdb.collegeplaying c 
ON p.playerid = c.playerid 
LEFT OUTER JOIN lahmansbaseballdb.allstarfull asf
ON asf.playerid = p.playerid
WHERE birthyear = 1985;

The preceding query will result in rows that have all the people table rows, but only the rows matching in the collegeplaying table and the allstarfull table that match the people table rows. The following screenshot shows the results of the previous query:

Note that in the preceding screenshot, you see NULL values in the yearid and gameid fields for many rows because those playerids didn't have a corresponding row in the allstarfull table. You will also see some schoolids that are NULL because those playerids didn't have corresponding rows in the collegeplaying table.

Additionally, you can use a left excluding join. This would include all rows from the left table that don't match records in the right table. 

To use a left excluding join, use the following syntax: 

SELECT column(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column
WHERE table2.column IS NULL;

The previous syntax shows you how to join two tables together with a left excluding join. This is a modified LEFT OUTER JOIN. You join a column in table1 that matches a column in table2.

The following example will help you to understand how to use the left excluding join: 

SELECT p.playerid, birthyear, schoolid, yearid 
FROM lahmansbaseballdb.people p 
LEFT OUTER JOIN lahmansbaseballdb.collegeplaying c 
ON p.playerid = c.playerid 
WHERE birthyear = 1985 
AND c.playerid IS NULL; 

The previous query will return the results in the following screenshot:

As you can see in the preceding screenshot, you are only getting rows from the left table (people) where the playerid in the right table (collegeplaying) is NULL. You can combine the results of the LEFT OUTER JOIN and the left excluding join with a set operator, which is covered later in this chapter.