PHP SQL - can someone explain outer join please

Author

can someone explain outer join please

Andrew Bullock

2005-01-19, 8:58 am

I understand (i think) left, right and inner,

but what does outer join do?

Thanks

Andrew

Steve

2005-01-19, 4:19 pm

An outer join can be LEFT, RIGHT or FULL.

SELECT a.col1, b.col2
FROM a LEFT OUTER JOIN b ON a.col1 = b.col2

An outer join like this fetches all the rows from one side of the join
- in this case, the LEFT side: a - with the rows from the other side
where the join condition is true. If there's no matching row on the
right, a row is still returned but the columns from the right side are
set to NULL.

If a.col1 = (1, 2, 3, 4, 5) and b.col2 = (1, 2, 4, 5) then the LEFT
OUTER JOIN result is:

+-------+-------+
|a.col1 |b.col2 |
+-------+-------+
|1 |1 |
|2 |2 |
|3 |NULL |
|4 |4 |
|5 |5 |
+-------+-------+

There's no such row with b.col2 = 3, so the result row has all b
columns = NULL.

With inner joins, you don't get rows where the join condition is not
true.

SELECT a.col1, b.col2
FROM a INNER JOIN b ON a.col1 = b.col2

This INNER JOIN gives fewer rows in the result:

+-------+-------+
|a.col1 |b.col2 |
+-------+-------+
|1 |1 |
|2 |2 |
|4 |4 |
|5 |5 |
+-------+-------+

Outer joins are often used to find missing data, by further restricting
the query to fetch only the unmatching rows:

SELECT a.col1
FROM a LEFT OUTER JOIN b ON a.col1 = b.col2
WHERE b.col2 IS NULL

This query returns only those rows in a that have no matching row in b
according to the join condition.
+-------+
|a.col1 |
+-------+
|3 |
+-------+

---
Steve

Sponsored Links