SQL Joins & Combinations SQL Joins & Combinations – INNER, LEFT, RIGHT, FULL, CROSS Explained
Introduction – Why Learn SQL Joins & Set Operations?
Real-world databases are relational, meaning data is spread across multiple tables. SQL joins and combinations allow you to query related data together, forming meaningful insights and complete reports.
Understanding how to combine tables with JOIN, UNION, and set operations like INTERSECT or EXCEPT helps you:
- Merge data efficiently
- Generate multi-table reports
- Build relationships between datasets
In this guide, you’ll learn how to:
- Use all types of joins:
INNER,LEFT,RIGHT,FULL, andCROSS - Combine query results using
UNION,INTERSECT, andEXCEPT - Apply self-joins to find relationships within the same table
Topics Covered
| Topic | Description |
|---|---|
| SQL INNER JOIN | Returns matching rows from both tables |
| SQL LEFT JOIN | Returns all rows from the left table, and matched rows from the right |
| SQL RIGHT JOIN | Returns all rows from the right table, and matched rows from the left |
| SQL FULL JOIN | Combines LEFT and RIGHT joins; returns all matched and unmatched rows |
| SQL SELF JOIN | Joins a table to itself to compare related rows |
| SQL UNION | Combines result sets of two queries and removes duplicates |
| SQL CROSS JOIN | Returns Cartesian product of two tables |
| SQL INTERSECT | Returns common rows from two queries |
| SQL EXCEPT / MINUS | Returns rows in one query that do not appear in another |
SQL INNER JOIN – Return Matching Records
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
Returns only rows that match in both tables.
Most commonly used join.
SQL LEFT JOIN – Return All from Left, Match from Right
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
Returns all rows from employees and matched rows from departments.
Unmatched right table values return NULL.
SQL RIGHT JOIN – Return All from Right, Match from Left
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
Opposite of LEFT JOIN.
Returns all rows from departments, matched rows from employees.
SQL FULL JOIN – All Matches and Unmatched Rows
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;
Combines LEFT and RIGHT JOIN results.
Unmatched rows from both tables are included with NULLs.
SQL SELF JOIN – Join a Table to Itself
SELECT a.name AS Employee, b.name AS Manager
FROM employees a
JOIN employees b
ON a.manager_id = b.id;
Useful for organizational hierarchies and recursive relationships.
Use aliases to differentiate the same table.
SQL UNION – Combine Result Sets Without Duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
Merges results from two queries with distinct values.
Columns must have same data types and count.
SQL CROSS JOIN – Cartesian Product
SELECT a.name, b.skill
FROM candidates a
CROSS JOIN skills b;
Combines every row of the first table with every row of the second.
Use cautiously; can produce huge datasets.
SQL INTERSECT – Common Records Between Queries
SELECT id FROM employees
INTERSECT
SELECT id FROM managers;
Returns only the records that exist in both result sets.
Not supported in all RDBMS (e.g., MySQL).
SQL EXCEPT / MINUS – Subtract Query Results
-- EXCEPT (PostgreSQL / SQL Server)
SELECT name FROM employees
EXCEPT
SELECT name FROM ex_employees;
-- MINUS (Oracle)
SELECT name FROM employees
MINUS
SELECT name FROM ex_employees;
Returns records from the first query not present in the second.
Syntax depends on the database engine.
Best Practices for Joins & Set Operations
| Best Practices | Common Pitfalls |
|---|---|
Always use table aliases (a, b) | Avoid ambiguous column references |
Prefer INNER JOIN for strict matches | Avoid CROSS JOIN without limits |
Ensure matching column data types in UNION | Don’t use UNION without deduplication in mind |
Use EXISTS or JOIN based on performance | Avoid FULL JOIN without checking NULLs |
Summary – Recap & Next Steps
SQL Joins and combinations are essential for working with normalized databases. Whether combining customer orders or joining employee departments, these tools enable complex and meaningful data retrieval.
Key Takeaways:
- Use
INNER,LEFT,RIGHT, andFULL JOINfor table-to-table relationships - Use
SELF JOINfor intra-table comparisons - Use
UNION,INTERSECT, andEXCEPTfor result set logic
Real-World Relevance:
Used in CRM systems, financial reports, HR applications, multi-table dashboards, and any business application using relational databases.
Next Topic: Deep dive into SQL GROUP BY & Aggregation Functions.
FAQs – SQL Joins & Set Operations
What’s the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows; LEFT JOIN returns all left-side rows and matches (if any) from the right.
Can I use JOIN with more than two tables?
Yes! You can chain multiple JOINs to combine several tables in one query.
What’s the difference between UNION and JOIN?
JOIN merges rows horizontally (side-by-side); UNION combines rows vertically (stacked result sets).
What’s the main use of CROSS JOIN?
To create all possible combinations of two tables, often used in matrix or permutation problems.
Do all databases support FULL JOIN and INTERSECT?
Not all. MySQL does not support FULL JOIN or INTERSECT natively without workarounds.
Share Now :
