Thursday, March 17, 2022

SQL cross join Example and it's performance

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.


SQL cross join Example and it's performance




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:

SQL CROSS JOIN Example



SQL CROSS JOIN Tutorials




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
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