Popular
Data Science
Technology
Finance
Management
Future Tech
This comprehensive guide provides in-depth insight into what CROSS JOIN in SQL is and helps you further understand the concept with simple examples.
New to SQL? Don’t have the slightest idea about SQL joins? Check this blog on what are SQL JOINs!
From the uses and applications to benefits and examples, learn everything about CROSS JOIN in SQL servers in this article.
If no WHERE clause is used in conjunction with the SQL CROSS JOIN, the result set is equal to the product of the rows in the first table times the rows in the second table. The term “Cartesian Product” refers to this kind of outcome. Where clause with CROSS JOIN performs similarly to an INNER JOIN when used together.
Relational databases are interacted with using it. Like an Excel spreadsheet, a regional database arranges data into tables. Small datasets and enormous stacks of data of any size can be understood and analyzed with SQL.
It is a strong tool that helps you carry out various tasks quickly and efficiently. It uses ‘non-procedural’ interactions with databases. Because of this, SQL’s syntax is fairly straightforward, and programmers only need to specify “what to do” rather than “how to do it.”
Every row in the initial table links up with every record in the second table when a statement with the CROSS JOIN clause is executed.
SQL’s NATURAL JOIN and CROSS JOIN operations are both fairly simple. A NATURAL JOIN uses the shared columns in the two connected tables to implicitly join them. Columns with the same name and datatype are referred to as common columns.
An INNER JOIN, LEFT OUTER JOIN, or RIGHT OUTER JOIN are all examples of NATURAL JOINS. On the other hand, CROSS JOIN generates a result set that includes all the properties of both tables, including common and duplicate columns. Let’s see the difference in SQL cross join with other joins in detail:
Join Type | Description | Syntax Example |
---|---|---|
CROSS JOIN | Returns the Cartesian product of two tables, all possible combinations of rows from both tables | |
INNER JOIN | Returns only the matching rows between two tables based on a specified join condition | |
LEFT JOIN | Returns all rows from the left (first) table and the matching rows from the right (second) table based on a specified join condition | |
RIGHT JOIN | Returns all rows from the right (second) table and the matching rows from the left (first) table based on a specified join condition | |
FULL JOIN | Returns all rows from both tables and includes matching and non-matching rows based on a specified join condition | |
SELF JOIN | Joins a table to itself, treating it as two separate instances, typically used to compare rows within the same table |
Read more about SQL Inner Join.
Here is what the syntax of a CROSS JOIN in SQL looks like:
Moreover, there’s also an alternative syntax for SQL CROSS JOIN, which doesn’t come with the CROSS JOIN keyword. Here it is:
For both these syntaxes, the output set will remain the same. Also, take note of a particular thing about the SQL CROSS JOIN. Compared to the FULL OUTER, LEFT, and INNER JOIN, the SQL CROSS JOIN alleviates the need for a joining condition.
Below is an SQL CROSS JOIN example between two tables:
Sample table: foods
Sample table: organization
You may want to get the organization city and organization name columns from the organization table. Also, you will need to get item units and item name columns from the foods table. So, once you perform CROSS joining on these tables, you can use this SQL statement:
SQL Code:
OR
Inner Join | CROSS JOIN | Full Outer Join |
It exhibits the matching records from multiple tables. | It generates an output set: the number of rows in the second table multiplied by the rows in the first or vice versa. | It exhibits both non-matching and matching records of the table. |
It is applicable to only a specified number of columns | It is applicable to each and every column | Nil |
It returns all combinations of rows from multiple tables. | It returns all combinations of rows from two tables. | It returns one distinct or unique row from each table |
To integrate all potential combinations of two or more tables, one can leverage the power of SQL CROSS JOIN. The result comprises every row from each and every contributing table.
Are you preparing for a SQL job? Is that why you’re revising topics like CROSS JOIN in SQL? If so, check this blog on SQL Interview Questions to solidify your preparation!
Imagine you wanted to ask a series of questions about a particular set of things and dates (availability, prices etc.). The objects and dates might be loaded into separate temporary tables, and your queries may cross-join the tables.
Given that certain databases have a restriction on the number of things in an IN clause, this may be more handy than the other option of listing the items and dates.
Here are a few examples of how SQL CROSS JOIN can be applied:
Also read about SQL Operators.
Let’s look at the example of CROSS JOIN in SQL with the help of table:
Bike Model | |
---|---|
1 | Pulsar |
2 | Splendor |
3 | Ninja |
+
Color Name | |
---|---|
1 | Green |
2 | Black |
3 | Red |
↓
CROSS JOIN |
↓
Bike Model | Color Name | |
---|---|---|
1 | Pulsar | Green |
2 | Splendor | Green |
3 | Ninja | Green |
4 | Pulsar | Black |
5 | Splendor | Black |
6 | Ninja | Black |
7 | Pulsar | Red |
8 | Splendor | Red |
9 | Ninja | Red |
Let’s say you have two tables (both database tables). While one table is about Bikes, enlisting all bike models with price quotes, the other table is on colors with a list of color names and cost of bikes in those respective colors.
Thus, getting every possible combination of bikes and colors will become essential. To do so, run this query command:
In the above example, we had three bike models available in three different colors. So, the SQL CROSS JOIN query returned nine output sets. If you look closely, you’ll find the number of rows in the second and first tables multiplied by each other.
There is no doubt in saying that SQL is:
So, if you’re interested in learning SQL and SQL Cross Join, a full-stack development course from HeroVired is the best way to get started.
SELECT * FROM table1 CROSS JOIN table2;
The DevOps Playbook
Simplify deployment with Docker containers.
Streamline development with modern practices.
Enhance efficiency with automated workflows.
Popular
Data Science
Technology
Finance
Management
Future Tech
Accelerator Program in Business Analytics & Data Science
Integrated Program in Data Science, AI and ML
Certificate Program in Full Stack Development with Specialization for Web and Mobile
Certificate Program in DevOps and Cloud Engineering
Certificate Program in Application Development
Certificate Program in Cybersecurity Essentials & Risk Assessment
Integrated Program in Finance and Financial Technologies
Certificate Program in Financial Analysis, Valuation and Risk Management
© 2024 Hero Vired. All rights reserved