SQL | Join (Cartesian Join & Self Join)
In SQL, CARTESIAN JOIN (also known as CROSS JOIN) and SELF JOIN are two distinct types of joins that help combine rows from one or more tables based on certain conditions. While both joins may seem similar, they serve different purposes. Let’s explore both in detail.
CARTESIAN JOIN
A Cartesian Join or CROSS JOIN returns the Cartesian product of two tables, meaning each row from the first table is combined with every row from the second table. This type of join does not require any specific condition or matching column between the two tables.
Syntax:
SELECT table1.column1 , table1.column2, table2.column1…
FROM table1
CROSS JOIN table2;
In the absence of a WHERE condition the CARTESIAN JOIN will behave like a CARTESIAN PRODUCT . i.e., the number of rows in the result-set is the product of the number of rows of the two tables. In the presence of WHERE condition this JOIN will function like a INNER JOIN.
Consider the following two tables Student and StudentCourse:
In a Cartesian Join, the number of rows in the result set is equal to the product of the number of rows in both tables. For instance, if Table A has 4 rows and Table B has 4 rows, the result set will contain 16 rows (4 * 4).
Query:
SELECT Student.NAME, Student.AGE, StudentCourse.COURSE_ID
FROM Student
CROSS JOIN StudentCourse;
Output:
Explanation:
In this example, each row from the Student table is joined with every row from the StudentCourse table, resulting in 16 rows (4 students * 4 courses). This is the Cartesian product of the two tables.
SELF JOIN
A Self Join is a join where a table is joined with itself. This type of join can be useful when we need to compare rows within the same table, such as when we want to join records from the same table based on some condition. In a self join, we use table aliases (e.g., a and b) to differentiate between the two instances of the same table.
Syntax:
SELECT a.coulmn1 , b.column2
FROM table_name a, table_name b
WHERE some_condition;
Example
SELECT a.ROLL_NO , b.NAME
FROM Student a, Student b
WHERE a.ROLL_NO < b.ROLL_NO;
Output:
Explanation:
In this self join example, the Student table is joined with itself. The query compares the ROLL_NO of two instances (aliased as a and b), and for each row, it returns the ROLL_NO of one record and the NAME of another record where a.ROLL_NO < b.ROLL_NO. This allows us to pair each student with other students that have a higher ROLL_NO.
Conclusion
In conclusion, both Cartesian Join (CROSS JOIN) and Self Join serve distinct purposes in SQL. A Cartesian Join produces a Cartesian product of two tables, combining each row from one table with every row from the other, resulting in potentially large result sets. In contrast, a Self Join is used to join a table with itself, typically to compare rows within the same table based on specific conditions.
FAQs
Difference between JOIN and Cartesian JOIN:
A
JOINcombines rows from two or more tables based on a related column, usually with a condition (e.g.,INNER JOIN,LEFT JOIN). ACartesian JOIN(orCROSS JOIN) returns the Cartesian product of the two tables, meaning it pairs every row from the first table with every row from the second table, without any condition.
Difference between JOIN and Self JOIN:
A
JOINcombines rows from two different tables based on a related column, while aSELF JOINis used to join a table to itself, typically when a record needs to be compared to other records within the same table.
Difference between Self JOIN and Cross JOIN:
A
SELF JOINjoins a table to itself with an alias, usually for comparing rows within the same table. ACROSS JOINreturns the Cartesian product of two tables, combining every row from one table with every row from another, without any filtering or comparison.
