FULL OUTER and SELF JOIN

FULL OUTER and SELF JOIN

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Matched rows are combined; unmatched rows show NULL on the missing side.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

• Customers with no order → OrderID is NULL.
• Orders with no customer → CustomerName is NULL.

Note: MySQL does not support FULL OUTER JOIN directly; SQL Server and PostgreSQL do.

SELF JOIN

A SELF JOIN joins a table with itself. Use it when rows in one table relate to other rows in the same table.

Example: find customers in the same city:

SELECT A.CustomerName, B.CustomerName, A.City
FROM Customers A, Customers B
WHERE A.City = B.City
AND A.CustomerID <> B.CustomerID;

• The table gets two aliases: A and B.
• Alias A is the "first" copy, alias B is the "second".
• The condition compares rows within the same table.

TL;DR

• FULL OUTER JOIN keeps everything from both tables.
• SELF JOIN joins a table to itself using two aliases.
• MySQL lacks FULL OUTER JOIN; use LEFT+UNION instead.