What are the different types of joins in SQL, and can you explain each join in detail?

Question

Accepted Answer

SQL joins combine rows from two or more tables based on related columns, enabling complex queries and data analysis.

INNER JOIN: Returns records with matching values in both tables. Example: SELECT * FROM A INNER JOIN B ON A.id = B.id;
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. Example: SELECT * FROM A LEFT JOIN B ON A.id = B.id;
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. Example: SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table. Example: SELECT * FROM A FULL JOIN B ON A.id = B.id;
CROSS JOIN: Returns the Cartesian product of two tables, combining all rows from both tables. Example: SELECT * FROM A CROSS JOIN B;
SELF JOIN: A join where a table is joined with itself. Example: SELECT A.name, B.name FROM A AS A, A AS B WHERE A.id = B.parent_id;

Top RDSolutions Data Quality Analyst interview questions & answers