DataPlayArena
SQL Reference Guide
Revision Time: 4 mins

9. Joins Reference

Relational joins in standard SQL.

INNER JOIN
Return: Table

Returns matched rows in both tables.

Syntax signature:SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;
Code snippet:
python
SELECT u.name, o.id FROM users u INNER JOIN orders o ON u.id = o.user_id;
Expected Output:Matching users and orders.
Remember: Always ensure correct syntax formatting when calling INNER JOIN.
LEFT JOIN
Return: Table

All left records, optional right matches.

Syntax signature:SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id;
Code snippet:
python
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
Expected Output:All users with optional orders info.
Remember: Always ensure correct syntax formatting when calling LEFT JOIN.
RIGHT JOIN
Return: Table

All right records, optional left matches.

Syntax signature:SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id;
Code snippet:
python
SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
Expected Output:All orders with optional users info.
Remember: Always ensure correct syntax formatting when calling RIGHT JOIN.
FULL JOIN
Return: Table

Union of both tables matched on keys.

Syntax signature:SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id;
Code snippet:
python
SELECT * FROM payroll FULL OUTER JOIN hr ON payroll.id = hr.emp_id;
Expected Output:Merged payroll and HR data.
Remember: Always ensure correct syntax formatting when calling FULL JOIN.
SELF JOIN
Return: Table

Join table with itself.

Syntax signature:SELECT * FROM t a JOIN t b ON a.col = b.col;
Code snippet:
python
SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;
Expected Output:Employees with manager labels.
Remember: Always ensure correct syntax formatting when calling SELF JOIN.
CROSS JOIN
Return: Table

Cartesian cross combinations.

Syntax signature:SELECT * FROM t1 CROSS JOIN t2;
Code snippet:
python
SELECT * FROM colors CROSS JOIN sizes;
Expected Output:Combinations matrix.
Remember: Always ensure correct syntax formatting when calling CROSS JOIN.
Previous Topic8. CASE StatementNext Topic10. Subqueries