SQL CROSS JOIN – Overview with Examples

Q: What is the example syntax of SQL CROSS JOIN?

The SQL CROSS JOIN example syntax, is: SELECT * FROM table1 CROSS JOIN table2;

Updated on March 22, 2024

Article Outline

What is CROSS JOIN in SQL?Importance of SQL in Database Management Comparison of CROSS JOIN to other Types of Joins in SQL Explanation of SQL CROSS JOIN Syntax Examples of CROSS JOIN in SQL Differences Between Inner Join, Outer Join, and Cross Join Uses of CROSS JOIN in SQL Benefits of CROSS JOIN in SQL Applications of CROSS JOIN in SQL Detailed Examples of CROSS JOIN in SQL Conclusion FAQs

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.

Table of Content –

What is CROSS JOIN in SQL?
Importance of SQL in database management
Significance of CROSS JOIN in SQL
Explanation of CROSS JOIN syntax in SQL
Examples of CROSS JOIN in SQL
Differences between inner join, outer join, and CROSS JOIN
Uses of CROSS JOIN
Benefits of CROSS JOIN in SQL
Applications of CROSS JOIN in SQL
Detailed examples of CROSS JOIN
Conclusion
FAQs

What is CROSS JOIN in SQL?

SQL CROSS JOIN - Overview with Examples

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.

Get curriculum highlights, career paths, industry insights and accelerate your technology journey.

Download brochure

Importance of SQL in Database Management

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

Comparison of CROSS JOIN to other Types of Joins in SQL

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	`SELECT * FROM Table1 CROSS JOIN Table2;`
INNER JOIN	Returns only the matching rows between two tables based on a specified join condition	`SELECT * FROM Table1 INNER JOIN Table2 ON Table1.column = Table2.column;`
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	`SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.column = Table2.column;`
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	`SELECT * FROM Table1 RIGHT JOIN Table2 ON Table1.column = Table2.column;`
FULL JOIN	Returns all rows from both tables and includes matching and non-matching rows based on a specified join condition	`SELECT * FROM Table1 FULL JOIN Table2 ON Table1.column = Table2.column;`
SELF JOIN	Joins a table to itself, treating it as two separate instances, typically used to compare rows within the same table	`SELECT * FROM Table t1 JOIN Table t2 ON t1.column = t2.column;`

Explanation of SQL CROSS JOIN Syntax

Here is what the syntax of a CROSS JOIN in SQL looks like:

SELECT ColumnName_1,
   	ColumnName_2,
   	ColumnName_N
FROM [Table_1]
 	CROSS JOIN [Table_2]

Moreover, there’s also an alternative syntax for SQL CROSS JOIN, which doesn’t come with the CROSS JOIN keyword. Here it is:

SELECT ColumnName_1,
   	ColumnName_2,
   	ColumnName_N
FROM [Table_1],[Table_2]

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.

Examples of CROSS JOIN in SQL

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:

SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city FROM foods CROSS JOIN company;

SELECT foods.item_name,foods.item_unit, organization.organization_name,organization.organization_city FROM foods,organization;

Differences Between Inner Join, Outer Join, and Cross Join

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

Uses of CROSS JOIN in SQL

SQL CROSS JOIN - Overview with Examples

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!

Benefits of CROSS JOIN in SQL

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.

Applications of CROSS JOIN in SQL

Here are a few examples of how SQL CROSS JOIN can be applied:

Combination Generation: SQL CROSS JOIN can be used to generate all possible combinations of values from multiple tables. This can be useful in scenarios such as generating test data or creating lookup tables for reference.
Calculating Aggregates: By using SQL CROSS JOIN with aggregate functions like SUM, COUNT, or AVG, it is possible to calculate aggregated values across multiple dimensions. For example, you can generate a grid of all possible combinations of products and regions and calculate the total sales for each combination.
Data Analysis: In some cases, a SQL CROSS JOIN can be used to analyze patterns or relationships between different attributes in a dataset. By combining all rows from two tables, you can perform calculations or comparisons to identify interesting insights or trends.
Simulating Operations: SQL CROSS JOIN can be used to simulate operations or scenarios where every combination of values needs to be considered. This can be useful in simulation or testing environments

Detailed Examples of CROSS JOIN in SQL

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:

SELECT
	 c.Car_model
	,c1.Color_name
FROM Cars c
CROSS JOIN Colors c1

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.

Conclusion

There is no doubt in saying that SQL is:

Leveraged by almost every tech-based company
Among the simplest and overall best coding languages for beginners
Open-source and simple-to-understand programming language

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.

FAQs

What is the CROSS JOIN in SQL?

The CROSS JOIN in SQL is leveraged to produce a linked or matched combination of every other row in the initial table with every other row in the second. The SQL CROSS JOIN is also referred to as Cartesian Join.

What is the example syntax of SQL CROSS JOIN?

The SQL CROSS JOIN example syntax, is:

SELECT * 
FROM table1 
CROSS JOIN table2;

What is the difference between full join and cross join in SQL?

The fact that all things are joined with one another in SQL CROSS JOIN means they won’t have any ON clauses. Meanwhile, a FULL OUTER JOIN is a perfect combination of RIGHT OUTER and LEFT OUTER JOINs.

What is the significance of CROSS JOIN in SQL

To create any combination of records from two tables, utilize the CROSS JOIN SQL query. For instance, if you have two columns named size and color and want a result set to show every potential paired combination, the CROSS JOIN will be useful.

How is SQL CROSS JOIN Used to Combine All Rows from Two or More Tables?

When you leverage CROSS JOIN in SQL server, you can receive the Cartesian product of the sets of rows. These are accessible from the linked tables. Thanks to SQL CROSS JOIN, you can combine every single record from a table with every single row from another.

Updated on March 22, 2024

Link

Upskill with expert articles

View all

Free courses curated for you

Basics of Python

5 Hrs. duration

Beginner level

9 Modules

Certification included

1800+ Learners

View

Essentials of Excel

4 Hrs. duration

Beginner level

12 Modules

Certification included

2200+ Learners

View

Basics of SQL

12 Hrs. duration

Beginner level

12 Modules

Certification included

2600+ Learners

View

Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Certification Program in Data Analytics

Technology

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

Finance