SQL INTERVIEW PREPARATION PART-20
What is the difference between a cross join and an inner join in SQL?
Answer:
- Cross Join:
- Produces a Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables.
- Does not require a condition.
- Can result in a large number of rows, especially if both tables are large.
Example:
SELECT *
FROM employees
CROSS JOIN departments;
This query returns every combination of rows from the employees and departments tables.
- Inner Join:
- Returns only the rows that have matching values in both tables based on a specified condition.
- Requires a condition to match rows from both tables.
- Generally returns fewer rows than a cross join because it filters the results to include only the matching rows.
Example:
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
This query returns the names of employees and their corresponding department names where there is a match between employees and departments based on department_id.
Tip: Explain that while cross joins can be useful for certain scenarios, they should be used with caution due to the potentially large result set. Inner joins are more commonly used to combine related data from multiple tables based on a logical relationship, ensuring more meaningful results.
You can refer these SQL Interview Resources to learn more
Like this post if you want me to continue this SQL series 👍♥️
Share with credits: https://t.me/sqlspecialist
Hope it helps :)