Outer Joins
SQL outer joins, including left outer joins, right outer joins, and full outer joins, automatically produce Nulls as placeholders for missing values in related tables. For left outer joins, for instance, Nulls are produced in place of rows missing from the table appearing on the right-hand side of the LEFT OUTER JOIN
operator. The following simple example uses two tables to demonstrate Null placeholder production in a left outer join.
The first table (Employee) contains employee ID numbers and names, while the second table (PhoneNumber) contains related employee ID numbers and phone numbers, as shown below.
|
|
The following sample SQL query performs a left outer join on these two tables.
SELECT e.ID, e.LastName, e.FirstName, pn.NUMBER FROM Employee e LEFT OUTER JOIN PhoneNumber pn ON e.ID = pn.ID;The result set generated by this query demonstrates how SQL uses Null as a placeholder for values missing from the right-hand (PhoneNumber) table, as shown below.
ID | LastName | FirstName | Number |
---|---|---|---|
1 | Johnson | Joe | 555-2323 |
2 | Lewis | Larry | NULL |
3 | Thompson | Thomas | 555-9876 |
4 | Patterson | Patricia | NULL |
Read more about this topic: Null (SQL)
Famous quotes containing the words outer and/or joins:
“After one look at this planet any visitor from outer space would say I WANT TO SEE THE MANAGER.”
—William Burroughs (b. 1914)
“All successful men have agreed in one thing,they were causationists. They believed that things went not by luck, but by law; that there was not a weak or a cracked link in the chain that joins the first and last of things.”
—Ralph Waldo Emerson (18031882)