In interviews you will be asked definitely a question on Joins. I am giving one of the basic question here.
- What is join?
When you want to see information from multiple tables, you can use a SELECT statement. SELECT statements can retrieve and join column values from two or more tables into a single row. The retrieval is based on a specified condition, typically of matching column values.
The main ingredient of a join is, typically, matching column values in rows of each table that participates in the join. The result of a join associates rows from one table with rows from another table. Depending on the type of join operation, some rows might be formed that contain column values in one table that do not match column values in another table.
DB2 for ZOS supports different kinds of joins. Those are:
Inner join – Combines each row of the left table with each row of the right table, keeping only the rows in which the join condition is true.
Outer join – Includes the rows that are produced by the inner join, plus the missing rows, depending on the type of outer join:
- Left outer join
- Includes the rows from the left table that were missing from the inner join.
- Right outer join
- Includes the rows from the right table that were missing from the inner join.
- Full outer join
- Includes the rows from both tables that were missing from the inner join.
We can specify joins in the FROM clause of a query. Data from the rows that satisfy the search conditions are joined from all the tables to form the result table.
Interview Questions on Joins:-
1) In the case of Left outer join, there is no matching rows of joined tables?
Best example for Joins. Reference IBM.