Null (SQL) - Outer Joins

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.

Employee
ID LastName FirstName
1 Johnson Joe
2 Lewis Larry
3 Thompson Thomas
4 Patterson Patricia
PhoneNumber
ID Number
1 555-2323
3 555-9876

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.

Query result
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:

    Once conform, once do what other people do because they do it, and a lethargy steals over all the finer nerves and faculties of the soul. She becomes all outer show and inward emptiness; dull, callous, and indifferent.
    Virginia Woolf (1882–1941)

    The peace loving nations must make a concerted effort in opposition to those violations of treaties and those ignorings of humane instincts which today are creating a state of international anarchy and instability from which there is no escape through mere isolation or neutrality.... When an epidemic of physical disease starts to spread, the community approves and joins in a quarantine of the patients in order to protect the health of the community against the spread of the disease.
    Franklin D. Roosevelt (1882–1945)