SEMI JOIN

SEMI JOIN is a SQL clause in the JOIN Types category. A conceptual join that returns rows from the left table where a match exists, without duplicating rows. The syntax is SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.col = table2.col);. It returns rows from left table with matches. A typical example: -- Semi-join via EXISTS: SELECT d.* FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id); -- Only departments that have employees (no join duplication) A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type. A close relative is LEFT JOIN, which returns ALL rows from the left table and matching rows from the right. Unmatched right rows show NULL. A close relative is RIGHT JOIN, which returns ALL rows from the right table and matching rows from the left. Opposite of LEFT JOIN.