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:
| ROLL_NO | NAME | ADDRESS | PHONE | AGE |
|---|---|---|---|---|
| 1 | Ryan Miller | New York | XXXXXXXXXX | 18 |
| 2 | James Wilson | Los Angeles | XXXXXXXXXX | 18 |
| 3 | Lucas Brown | Chicago | XXXXXXXXXX | 20 |
| 4 | Daniel Smith | San Francisco | XXXXXXXXXX | 18 |
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:
| NAME | AGE | COURSE_ID |
|---|---|---|
| Ryan Miller | 18 | 1 |
| Ryan Miller | 18 | 2 |
| Ryan Miller | 18 | 2 |
| Ryan Miller | 18 | 3 |
| James Wilson | 18 | 1 |
| James Wilson | 18 | 2 |
| James Wilson | 18 | 2 |
| James Wilson | 18 | 3 |
| Lucas Brown | 20 | 1 |
| Lucas Brown | 20 | 2 |
| Lucas Brown | 20 | 2 |
| Lucas Brown | 20 | 3 |
| Daniel Smith | 18 | 1 |
| Daniel Smith | 18 | 2 |
| Daniel Smith | 18 | 2 |
| Daniel Smith | 18 | 3 |
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:
| ROLL_NO | NAME |
|---|---|
| 1 | James Smith |
| 1 | Lucas Brown |
| 2 | Lucas Brown |
| 1 | Daniel Wilson |
| 2 | Daniel Wilson |
| 3 | Daniel Wilson |
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.