Cross Join in SQL
Are you looking to combine data from two or more tables in SQL? SQL Cross Join is an efficient and straightforward way to do this. With this method, you can generate a new table that includes all possible combinations of data from the two original tables. In this article, we will introduce you to SQL Cross Join and provide a step-by-step guide on how to use it effectively to combine multiple tables.
In the previous articles, we have discussed different types of Joins in SQL, such as Inner Join, Left Join, and Right Join. This article will discuss another type of SQL Join, i.e., Cross Join.
Must Check: Free Database and SQL Online Course and Certificates
Must Check: SQL tutorial
What is SQL Cross Join?
Similar to other Joins in SQL, cross-join is used to combine two or more different tables. Cross-join in SQL combines each row of one table with each row of another table and returns the cartesian product of all the table elements.
In simple terms, cross-join in SQL returns all the possible combinations of both tables.
- The result set of Cross Join is given in the ordered pair in which the first element is from Table 1 and the second element is from Table 2.
- The number of rows in the result set is equal to the product of the number of rows in the first table and the number of rows in the second table.
Read Also: Update Query in SQL
The product of cross join can be easily understood by the given below example:
The above picture contains the two sets, each containing three elements, and when the cross join (cartesian product) is performed, the result set will contain nine elements.
Let’s take an example to get a better understanding of how SQL cross works:
Example: Let there be two tables, table 1 ( containing student name) and table 2 ( containing subject name), and when we cross join both the tables, we will get the following result.
Syntax
The query of Cross Join can be written in two ways:
1st Method:
SELECT column_namesFROM table_1 CROSS JOIN table_2;
2nd Method:
SELECT column_namesFROM table_1, table_2;
Must Read: SQL Logical Operator
Note:
- Both methods will produce the same result
- Unlike the Inner Join, Left Join, and Right Join, SQL Cross Join does not require any joining conditions.
Now, let’s use the same above example and write the query to produce the result:
Firstly we will create both Student and Subject tables:
CREATE TABLE Student ( Id int PRIMARY KEY Student Name VARCHAR (56));
INSERT INTO Student VALUES (1, Kartik);INSERT INTO Student VALUES (2, Amit);INSERT INTO Student VALUES (2, Shewtank);
SELECT *FROM Student;
The above query will produce the following table:
| Id | Student Name |
| 1 | Kartik |
| 2 | Amit |
| 3 | Shewtank |
CREATE TABLE Subject ( Id int PRIMARY KEY Subject Name VARCHAR (56));
INSERT INTO Subject VALUES (1, English);INSERT INTO Subject VALUES (2, Hindi);INSERT INTO Subject VALUES (2, Math);
SELECT *FROM Subject;
The above query will produce the following result:
| Id | Subject Name |
| 1 | Engish |
| 2 | Hindi |
| 3 | Math |
Must Read: SQL LIMITS
Now, finally, we will write the query to combine both table using cross join:
SELECT st.Student Name, su.Subject NameFROM Student stCROSS JOIN Subject su;
The above query will produce the following table:
| Student Name | Subject Name |
| Kartik | English |
| Amit | English |
| Shewtank | English |
| Kartik | Hindi |
| Amit | Hindi |
| Shewtank | Hindi |
| Kartik | Math |
| Amit | Math |
| Shewtank | Math |
We were looking for this; the cross-join query (the result set) returns the same nine entries.
Till now, we have learned how to join two tables using cross join, and now we will see how to join more than two tables using SQL Cross Join.
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
SQL CROSS JOIN with Three Different Tables
Let there be three different tables Menu, Restaurant Name, and Location
Restaurant Name:
| Id | Restaurant Name |
| 1 | Evergreen |
| 2 | Hudson Cafe |
Menu
| Id | Food Item |
| 1 | Chole Bhature |
| 2 | Lassi |
| 3 | Diet Coke |
Location
| Id | Location |
| 1 | Green Park |
| 2 | Mukherjee Nagar |
Now, we want to see all the possible combinations where these food items are available. The below query will help you to find all the possible combinations:
SELECT m.Food Item AS Item, r.Restaurant Name, l.Location
FROM Menu mCROSS JOIN Restaurant rCROSS JOIN Location l;
Output
| Item | Restaurant Name | Location |
| Chole Bhature | Evergreen | Green Park |
| Lassi | Evergreen | Green Park |
| Diet Coke | Evergreen | Green Park |
| Chole Bhature | Hudson Cafe | Green Park |
| Lassi | Hudson Cafe | Green Park |
| Diet Coke | Hudson Cafe | Green Park |
| Chole Bhature | Evergreen | Mukherjee Nagar |
| Lassi | Evergreen | Mukherjee Nagar |
| Diet Coke | Evergreen | Mukherjee Nagar |
| Chole Bhature | Hudson Cafe | Mukherjee Nagar |
| Lassi | Hudson Cafe | Mukherjee Nagar |
| Diet Coke | Hudson Cafe | Mukherjee Nagar |
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Conclusion
SQL CROSS JOIN generates Cartesian products from multiple tables. It pairs each row of one table with every row of another, creating extensive combinations of data. While simple in syntax, it can impact database performance. Use it judiciously and understand its implications on data processing. Mastering CROSS JOIN is crucial for comprehensive data analysis and manipulation.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!
FAQs on Cross-Join in SQL
What is a CROSS JOIN in SQL?
A CROSS JOIN in SQL is used to combine each row of one table with every row of another table, resulting in a Cartesian product of the two tables. This type of join does not require a join condition.
Can a CROSS JOIN be performed with more than two tables?
Yes, a CROSS JOIN can be executed with more than two tables, producing every possible combination of rows among the involved tables.
What are some common uses of a CROSS JOIN?
CROSS JOINs are often used in scenarios like generating a comprehensive price list for products across multiple customers, creating extensive test data, or when every combination of rows from the joined tables is required for analysis.
How does the result set size of a CROSS JOIN get determined?
The size of the result set from a CROSS JOIN is the product of the number of rows in the joined tables. For instance, if one table has 3 rows and the other has 4, the result set will have 12 rows (3 x 4).
Are there performance considerations with CROSS JOINs?
Yes, CROSS JOINs can be resource-intensive and may affect performance, especially when joining large tables. The result set can grow exponentially with the number of rows in the involved tables, leading to potential performance issues.
Can CROSS JOINs be used in SQL Server?
CROSS JOINs are applicable in SQL Server and other database systems. They follow the same principle of combining each row of one table with every row of another table.
How does the result of a CROSS JOIN differ from other types of joins?
Unlike INNER JOIN, LEFT JOIN, or FULL OUTER JOIN, CROSS JOIN does not rely on a join condition to match rows. It simply combines all rows from the joined tables.
Is there a way to concatenate results in a CROSS JOIN?
Yes, functions like
CONCAT_WS
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
