🔗 Joins
Every join flavor: inner, outer, anti, self and cross joins with real predicates.
| Snippet | |
|---|---|
| ☆INNER JOIN — Rows matching in both tables |
SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id = d.id;
|
| ☆LEFT JOIN — All left rows plus matches |
SELECT e.name, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
|
| ☆FULL OUTER JOIN — Matched rows plus unmatched from both sides |
SELECT e.name, d.name FROM emp e FULL OUTER JOIN dept d ON e.dept_id = d.id;
|
| ☆SELF JOIN — Join a table to itself (employee to manager) |
SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id;
|
| ☆ANTI-JOIN — Parent rows with no children |
SELECT d.name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id WHERE e.id IS NULL;
|
| ☆OUTER JOIN (+) — Legacy Oracle outer-join syntax |
SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id(+);
|
| ☆CROSS JOIN — Cartesian product of both tables |
SELECT a.name, b.name FROM emp a CROSS JOIN dept b;
|
| ☆SELF JOIN (ALT) — Self join with column aliases |
SELECT e1.name, e2.name AS mgr FROM emp e1 JOIN emp e2 ON e1.mgr_id = e2.id;
|