FULL OUTER and SELF JOIN

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

sql

### 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:

sql

### 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.