What are joins and name the types? Why is self join useful?

Question

Accepted Answer

Joins are used to combine data from two or more tables based on a related column. Types include inner, outer, left, right, and cross join.

Inner join returns only the matching rows from both tables.
Outer join returns all rows from both tables, with null values for non-matching rows.
Left join returns all rows from the left table and matching rows from the right table.
Right join returns all rows from the right table and matching rows from the left table.
Cross join returns the Cartesian product of both tables.
Self join is useful when a table needs to be joined with itself, such as when comparing data within the same table.
Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example: SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column WHERE table2.column IS NULL;

Top IQVIA Associate interview questions & answers