Introduction
In SQL Interview Questions, SQL Joins are essential when working with relational databases, as they allow us to combine records from two or more tables based on common columns. In this blog, we will explore different types of SQL joins and determine the total number of records obtained using Inner Join, Left Join, and Right Join for the given tables.
What are SQL Joins?
Joins in SQL are used to fetch data from multiple tables based on a related column between them. As SQL Interview Questions purpose we need to prepare on this topic. Each type of join has a distinct function:
- INNER JOIN: Returns only the matching rows from both tables.
- LEFT JOIN: Returns all records from the left table and matching records from the right table.
- RIGHT JOIN: Returns all records from the right table and matching records from the left table.
Explanation of Different Types of Joins
Inner Join
In an inner join, only records with values that match in both tables are returned.
Left Join
Every record in the left table and the corresponding records in the right table are returned by a left join. NULL values are returned for columns from the right table if there is no match.
Right Join
Right Join works similarly to Left Join but includes all records from the right table and the matching records from the left table.
Understanding the Above Given Tables
Table1:
ID
1
1
2
3
3
3
5
Table2:
ID
1
1
1
2
2
3
3
3
4
Finding the Total Number of Records for Each Join
Inner Join Record Count
Inner Join will return only the IDs that exist in both tables. Matching IDs are 1, 2, and 3. Considering duplicates:
- ID 1 appears twice in table1 and three times in table2 → 2 x 3 = 6
- ID 2 appears once in table1 and twice in table2 → 1 x 2 = 2
- ID 3 appears three times in table1 and three times in table2 → 3 x 3 = 9
Total records in INNER JOIN = 6 + 2 + 9 = 17
Left Join Record Count
Left Join includes all records from table1:
- All 7 records from table1 remain.
- IDs 1, 2, 3 have matches, so they expand as per INNER JOIN count.
- ID 5 has no match in table2, so it appears with NULL.
Total records in LEFT JOIN = INNER JOIN (17) + unmatched (1) = 18
Right Join Record Count
Right Join includes all records from table2:
- All 9 records from table2 remain.
- IDs 1, 2, 3 expand as per INNER JOIN count.
- ID 4 has no match in table1, so it appears with NULL.
Total records in RIGHT JOIN = INNER JOIN (17) + unmatched (1) = 18
SQL Queries for Each Join Type
Inner Join Query
SELECT t1.ID, t2.ID
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.ID;
Left Join Query
SELECT t1.ID, t2.ID
FROM table1 t1
LEFT JOIN table2 t2
ON t1.ID = t2.ID;
Right Join Query
SELECT t1.ID, t2.ID
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.ID = t2.ID;
Conclusion
Understanding SQL Joins is crucial for database querying and optimization. From our example:
- INNER JOIN returned 17 records (only matches).
- LEFT JOIN returned 18 records (all left table records + NULLs).
- RIGHT JOIN returned 18 records (all right table records + NULLs).
FAQs
1. What is the main difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching records, while LEFT JOIN returns all records from the left table and NULLs for unmatched rows from the right table.
2. Can RIGHT JOIN be used instead of LEFT JOIN?
Yes, RIGHT JOIN can be used, but it depends on which table you want to include all records from.
3. What happens if there are no matching records in INNER JOIN?
If there are no matching records, INNER JOIN will return an empty result set.
4. How do NULL values appear in LEFT JOIN and RIGHT JOIN?
NULL values appear in columns of the right table when using LEFT JOIN and in columns of the left table when using RIGHT JOIN if no matching records are found.
5. Which JOIN is best for performance?
INNER JOIN is generally the most efficient as it only returns matching records, reducing unnecessary data retrieval.
6. How do I count the number of records in a JOIN query?
You can use the COUNT function:
SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t1.ID = t2.ID;
7. Can we join more than two tables?
Yes, SQL allows joining multiple tables using multiple JOIN conditions.
8. What is a FULL OUTER JOIN, and why is it not mentioned?
All records from both tables, including those that don’t match, are returned by a FULL OUTER JOIN.It is not directly supported in MySQL but can be achieved using UNION of LEFT and RIGHT JOINs.
9. Are there performance considerations for using JOINs?
Yes, using indexes on joined columns improves performance significantly, reducing scan time and query execution duration.
10. Can JOINs be used in subqueries?
Yes, JOINs can be used within subqueries to fetch related data dynamically.