The difference between CROSS JOIN and INNER JOIN in SQL
The question here is about the difference between a CROSS JOIN and an INNER JOIN in SQL. While both can be used to combine rows from two or more tables, they do so in distinct ways:
- CROSS JOIN: Combines each row of the first table with each row of the second table. If the first table has ‘A’ rows and the second table has ‘B’ rows, the result will have A x B rows.
-
INNER JOIN: Combines rows from two tables based on a given condition. Only rows that satisfy the condition are included in the result.
Input
For our demonstration, let’s consider two tables: Users and Products.
-- Creating the Users table
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName VARCHAR(255)
);
-- Inserting data into the Users table
INSERT INTO Users VALUES (1, 'Alice');
INSERT INTO Users VALUES (2, 'Bob');
-- Creating the Products table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255)
);
-- Inserting data into the Products table
INSERT INTO Products VALUES (1, 'Laptop');
INSERT INTO Products VALUES (2, 'Phone');
Users Table
| UserID | UserName |
|---|---|
| 1 | Alice |
| 2 | Bob |
Products Table
| ProductID | ProductName |
|---|---|
| 1 | Laptop |
| 2 | Phone |
Try Hands-On: HERE
How CROSS JOIN Works?
If first table has ‘n’ rows and second table has ‘m’ rows, then the output will have all possible combinations of rows from both the tables, resulting in m*n rows.
SELECT * FROM Users CROSS JOIN Products;
RESULT:
| UserID | UserName | ProductID | ProductName |
|---|---|---|---|
| 1 | Alice | 1 | Laptop |
| 1 | Alice | 2 | Phone |
| 2 | Bob | 1 | Laptop |
| 2 | Bob | 2 | Phone |
How INNER Join works?
Let’s say we want to join on a hypothetical condition. For instance, if UserID and ProductID are the same (just as an example). We’d then use an INNER JOIN like this:
SELECT *
FROM Users
INNER JOIN Products ON Users.UserID = Products.ProductID;
RESULT:
| UserID | UserName | ProductID | ProductName |
|---|---|---|---|
| 1 | Alice | 1 | Laptop |
| 2 | Bob | 2 | Phone |
Only rows where UserID matches ProductID are included in the result.
More Explanation:
- A CROSS JOIN does not rely on a condition. Instead, it produces a Cartesian product of the two tables, which means every possible combination of rows.
-
An INNER JOIN combines rows based on the condition specified in the ON clause. Only rows that satisfy this condition are included in the result.
In real-world scenarios, you’d typically use INNER JOIN much more often than CROSS JOIN because you’d typically want to combine data based on specific conditions or relations between tables.
