Join in SQL is helpful to get rows from multiple tables. Below are common join-related interview questions.
SQL Join: Interview Questions
1. What are the different types of joins available?
These include INNER join, LEFT OUTER join, RIGHT OUTER join, and FULL OUTER join. The data from these joins is stored in an intermediate table, allowing further analysis.
2. What is the difference between an INNER and an OUTER join?
The INNER join combines rows from two tables based on a common condition, including only the rows that meet the condition. The OUTER join includes non-matching rows from both tables in the result set.
3. How does the RDBMS decide which rows to include in the answer set with an OUTER join?
- LEFT OUTER join: All the records from the left table (called the “left” because it appears before the JOIN keyword) are returned, along with the matching records from the right table. If there are no matching records in the right table, it assigns NULL values for those columns.
- RIGHT OUTER join: Similar to the LEFT OUTER join, all the records from the right table are returned, along with the matching records from the left table. If there are no matching records in the left table, it assigns NULL values for those columns.
- FULL OUTER join: Returns all the records from both the left and right tables, including both matching and non-matching records. If there are no matching records, it assigns NULL values for those columns.
4. What is the most efficient type of join?
In practice, all joins are inefficient. Only if needed, you need to use Joins.
5. Must two different tables be used to perform a join?
It is not necessary. You can use the same table to join (self-join interview questions). That is called self-join.
SELECT * FROM A.PERSONNEL, B.PERSONNEL
WHERE A.BDATE =B.BDATE
AND A.SEX <> B.SEX
AND A.SEX = 'M'6. Which join methods does DB2 use to achieve a join?
There are three methods – nested loop join, a merge scan join, and a hybrid join. The DB2 optimizer chooses the method it calculates to be the most efficient one to perform the join (A multiple tables join can involve more than one join method.)
7. Can you explain the nested loop join method?
Nested loop join is a method of joining two tables in a database. It involves iterating through each row of one table and comparing it to every row of the other table to find matching records. This process repeats until all possible matches found.
8. When is it best to use a nested loop join?
The nested loop is most efficient when the inner table has an index on the join column arranged sequentially. It is also efficient when the number of rows retrieved from the inner-table (via an index) is small. The optimizer chooses it when the outer table is small /or the local predicates (conditions) significantly reduce the number of qualifying rows in the outer-table.
9. What is a merge join, and how is it processed?
Merge scan is an efficient join algorithm used to combine two sorted datasets into one. By scanning both datasets in parallel and matching the rows with the same key value, merge scan quickly merges the matching rows into a new dataset.
10. When is the merge join method used?
When the optimizer identifies a many-to-many join, large tables, no matching index, and join conditions that have little filtration effect, it utilizes this technique.
11. What is a hybrid join?
A hybrid join is a database technique that combines hash-join and merge join methods to optimize the join operation and improve query performance. The optimizer executes both join techniques and chooses the more efficient one based on data size and other factors.
12. What is a cartesian join?
Every row of the outer table combines with every row of the inner table.
Srinimf 