Hello guys, in the past, I gave you a quick overview of SQL JOINS where we learned about different types of JOINS like right, left, inner, and outer join, and in this article, we'll learn about the SQL CROSS JOIN idea in this post, and we'll back up our knowledge with simple examples and illustrations. So, let's start with the basics.
What is SQL cross join?
Cartesian
or cross joins are used when two or more tables are joined together
without any conditions (where or on). Each record of one table is
connected with each record of the other table participating in the join
in a Cross Join. In SQL Server, neither the ON nor the where clause
should be present in the Cross Join.
The
CROSS JOIN joins each row of the first table with each row of the second
table to create a matched combination. Cartesian join is another name
for this form of connection.
Let's pretend
we're in a coffee shop and decide to get breakfast. We'll take a peek at
the menu and decide which meal and drink combo would be the most
delectable. This signal will be received by our brain, which will then
begin to produce all possible food and drink pairings.
Cross Join
syntax:
SELECT ColumnName_1, ColumnName_2, ColumnName_N FROM [Table_1] CROSS JOIN [Table_2];
Now,
to demonstrate an example for cross join, let's create a database and
insert some data into it to visualize better. For the same purpose, use
the below queries to create 2 tables. Remember guys, we need 2 tables as
we are practicing cross joins in this article. we will use our old
database of cricketers and franchise tables. But, to make the process of
our readers hassle-free, we will recreate the tables.
CREATE TABLE cricketer ( id INT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, franchise VARCHAR(11) NOT NULL, price INT NOT NULL );
Now, let's insert some data into our table
INSERT INTO cricketer(id, name, franchise, price) VALUES (11, 'Virat','rcb', 501), ( 3, 'Rohit','mi', 201), ( 5, 'Bumrah','mi', 401), ( 7, 'Jadeja','rcb', 101), ( 8, 'Dhoni','mi', 401), ( 2, 'Surya','rcb', 51);
Similarly, let's create the franchise table.
CREATE TABLE franchise ( id INT NOT NULL PRIMARY KEY, fname VARCHAR(101) NOT NULL, budget_cr INT NOT NULL ); INSERT INTO franchise(id, fname, budget_cr) VALUES (1, 'Gujarat Titans', 1001), (2, 'Royal challengers banglore', 901), (3, 'Mumbai Indians', 9999), (4, 'Lucknow super giants', 201);
The created tables look something as shown below:
Now, let's do a cross join on these two tables.
SELECT * FROM cricketer CROSS JOIN franchise;
The result would be something as shown below:
(As the list was too long, I had to paste it as markdown text)
| id | name | franchise | price | id | fname | budget_cr |
| --- | ------ | --------- | ----- | --- | -------------------------- | --------- |
| 2 | Surya | rcb | 51 | 1 | Gujarat Titans | 1001 |
| 2 | Surya | rcb | 51 | 2 | Royal challengers banglore | 901 |
| 2 | Surya | rcb | 51 | 3 | Mumbai Indians | 9999 |
| 2 | Surya | rcb | 51 | 4 | Lucknow super giants | 201 |
| 3 | Rohit | mi | 201 | 1 | Gujarat Titans | 1001 |
| 3 | Rohit | mi | 201 | 2 | Royal challengers banglore | 901 |
| 3 | Rohit | mi | 201 | 3 | Mumbai Indians | 9999 |
| 3 | Rohit | mi | 201 | 4 | Lucknow super giants | 201 |
| 5 | Bumrah | mi | 401 | 1 | Gujarat Titans | 1001 |
| 5 | Bumrah | mi | 401 | 2 | Royal challengers banglore | 901 |
| 5 | Bumrah | mi | 401 | 3 | Mumbai Indians | 9999 |
| 5 | Bumrah | mi | 401 | 4 | Lucknow super giants | 201 |
| 7 | Jadeja | rcb | 101 | 1 | Gujarat Titans | 1001 |
| 7 | Jadeja | rcb | 101 | 2 | Royal challengers banglore | 901 |
| 7 | Jadeja | rcb | 101 | 3 | Mumbai Indians | 9999 |
| 7 | Jadeja | rcb | 101 | 4 | Lucknow super giants | 201 |
| 8 | Dhoni | mi | 401 | 1 | Gujarat Titans | 1001 |
| 8 | Dhoni | mi | 401 | 2 | Royal challengers banglore | 901 |
| 8 | Dhoni | mi | 401 | 3 | Mumbai Indians | 9999 |
| 8 | Dhoni | mi | 401 | 4 | Lucknow super giants | 201 |
| 11 | Virat | rcb | 501 | 1 | Gujarat Titans | 1001 |
| 11 | Virat | rcb | 501 | 2 | Royal challengers banglore | 901 |
| 11 | Virat | rcb | 501 | 3 | Mumbai Indians | 9999 |
| 11 | Virat | rcb | 501 | 4 | Lucknow super giants | 201 |
As you guys can see, all the combinations are taken into consideration while performing cross joins.
Performance considerations
SQL
queries with the CROSS JOIN keyword can be highly expensive. We're
attempting to convey that these searches have a significant risk of
using more resources and causing performance problems.
Simply
considered, SQL cross joins produce every output that possibly can, and
thus, is very costly. taking the example of our cricketer, franchise
tables, our cricketer table had 6 entries and our franchise table had 4
entries.
Cross join simply matches each cricketer table row with a
franchise table row, resulting in 6x4 = 24 records which we can see in
the markdown output provided above.
Thus
we should avoid using cross joins whenever possible. If there is no
other way, we should definitely use a WHERE clause which would behave as
a filter.
If
you guys have an apex SQL plan, you can write the above query and check
out the SQL plan generated for the query. It would clearly show the
performance issues and mention a warning that no filter is used such as
WHERE.
Conclusion
We
covered the foundations of SQL CROSS JOIN in detail in this post, as
well as performance concerns for the CROSS JOIN. When CROSS JOIN is used
for tables with a large number of rows, it may have a detrimental
impact on performance.
Other SQL Articles and Tutorials you may like
- How to find Nth Highest salary in SQL
- Difference between DDL and DML commands in SQL
- How to remove duplicate rows in SQL
- How to use ORDER BY in SQL with example
- How to use row_number function in SQL
- MySQL vs PostgreSQL? Pros and Cons
- What is a Virtual column in MySQL? Example
Thanks for reading this article so far. If you like this SQL CROSS JOIN tutorial, example, and performance explanation then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.
No comments:
Post a Comment