SQL OUTER JOIN: Left, Right, and Full
An OUTER JOIN combines data from two tables ÔÇö including both matching and non-matching records. SQL supports three types: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
Complete Python Course with Advance topics:-
SQL Tutorial:-
1. LEFT OUTER JOIN
Returns all rows from the LEFT table and matching rows from the right. NULL appears where no match exists.
SELECT t1.col1, t2.col2
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.ColumnName = t2.ColumnName;2. RIGHT OUTER JOIN
Returns all rows from the RIGHT table and matching rows from the left. NULL appears where no match exists.
SELECT e.EmployeeID, e.Employee_Name, d.Department_Name
FROM employee e
RIGHT OUTER JOIN department d ON e.EmployeeID = d.Employee_ID;3. FULL OUTER JOIN
Returns ALL rows from both tables, with NULLs where no match exists. Supported in SQL Server, Oracle, PostgreSQL.
SELECT t1.col1, t2.col2
FROM Table1 t1
FULL OUTER JOIN Table2 t2
ON t1.ColumnName = t2.ColumnName;FULL OUTER JOIN in MySQL (Workaround)
MySQL does not support FULL OUTER JOIN natively. Use UNION of LEFT and RIGHT joins:
SELECT t1.col1, t2.col2 FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.col = t2.col
UNION
SELECT t1.col1, t2.col2 FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.col = t2.col;Choosing the Right Outer Join
- Need all rows from the left table? → LEFT OUTER JOIN
- Need all rows from the right table? → RIGHT OUTER JOIN
- Need every row from both? → FULL OUTER JOIN
Download New Real Time Projects:- Click here
Complete Advance AI topics:-
Conclusion
OUTER JOINs let you keep unmatched records that an INNER JOIN would drop. Mastering LEFT, RIGHT, and FULL gives you complete control over multi-table reporting. For more SQL tutorials, stay tuned to .
sql left outer join
sql outer join oracle
mysql outer join
sql outer join example
sql full outer join
sql joins
right outer join
self join in sql
