Cross Join in Sql with examples


Cross Join in Sql

In SQL, a CROSS JOIN is a type of join that returns all possible combinations of rows from the tables being joined. It is sometimes called a cartesian join because it produces a result set that is the cartesian product of the sets of rows from the joined tables. For example, if table A has 3 rows and table B has 2 rows, a CROSS JOIN of these tables will return 6 rows (3 x 2 = 6).

The syntax for a CROSS JOIN in SQL is:

SELECT *
FROM table1
CROSS JOIN table2;

This will return all rows from table1 combined with all rows from table2. Note that if both tables have a column with the same name, the resulting table will have two columns with the same name, which may cause ambiguity in your query. In this case, you can use table aliases to specify which table each column belongs to. For example:

SELECT table1.col1, table1.col2, table2.col1, table2.col2
FROM table1
CROSS JOIN table2;

Here, we are selecting specific columns from each table and using table aliases (table1 and table2) to avoid ambiguity.

A CROSS JOIN in SQL is a useful way to generate a large number of rows quickly, but it can also be very inefficient if one of the tables being joined is very large. In general, it is better to use a CROSS JOIN only when you know that the tables being joined are small, or when you have some other way of limiting the number of rows in the result set (e.g. using a WHERE clause).

Here are some examples of using CROSS JOIN in SQL:

Suppose we have two tables, students and courses, with the following data:

students:
+—-+——-+
| id | name |
+—-+——-+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+—-+——-+

courses:
+—-+—————-+
| id | course_name |
+—-+—————-+
| 1 | Math |
| 2 | English |
| 3 | Computer Science |
+—-+—————-+

If we want to see all the possible combinations of students and courses, we can use a CROSS JOIN:

SELECT *
FROM students
CROSS JOIN courses;

This would produce the following result:

+—-+——-+—-+—————-+
| id | name | id | courses_name |
+—-+——-+—-+—————-+
| 1 | Ally | 1 | Math |
| 1 | Ally | 2 | English |
| 1 | Ally | 3 | Computer Science |
| 2 | Boby | 1 | Math |
| 2 | Boby | 2 | English |
| 2 | Boby | 3 | Computer Science |
| 3 | Carols | 1 | Math |
| 3 | Carols | 2 | English |
| 3 | Carols | 3 | Computer Science |
+—-+——-+—-+—————-+

Notice that this produces 9 rows, which is the cartesian product of the two tables (3 rows x 3 rows = 9 rows).

Another example is if we want to see all the possible combinations of colors and shapes:

colors:
+—-+——-+
| id | name |
+—-+——-+
| 1 | red |
| 2 | green |
| 3 | blue |
+—-+——-+

shapes:
+—-+——–+
| id | name |
+—-+——–+
| 1 | square |
| 2 | circle |
| 3 | triangle |
+—-+——–+

We can use a CROSS JOIN to see all the possible combinations of colors and shapes:

SELECT *
FROM colors
CROSS JOIN shapes;

This would produce the following result:

+—-+——-+—-+——–+
| id | name | id | name |
+—-+——-+—-+——–+
| 1 | red | 1 | square |
| 1 | red | 2 | circle |
| 1 | red | 3 | triangle |
| 2 | green | 1 | square |
| 2 | green | 2 | circle |
| 2 | green | 3 | triangle |
| 3 | blue | 1 | square |
| 3 | blue | 2 | circle |
| 3 | blue | 3 | triangle |
+—-+——-+—-+——–+

This produces 9 rows, which is the cartesian product

Blog Author Yogiraj Avatar

Author: Yogiraj

Raj is a Top Rated full stack web developer and is a very good technical writer. He is a veteran of Digital Marketing and Web development and having expertise in WordPress, Codeigniter, Core PHP Applications, Mysql and excellent in debugging the codes. Being a veteran in web development Raj has extensive knowledge of various technical aspects of web development, which he always shares in his blogs so that others can be benefited from his experience.

Share on: Share YogiRaj B.Ed Study Notes on twitter Share YogiRaj B.Ed Study Notes on facebook Share YogiRaj B.Ed Study Notes on WhatsApp

Suggested Posts

Export mysql database | Complete tutorial
Export mysql database | Complete tutorial

Exporting a MySQL database is a common task that is often performed for backup and migration purposes. MySQL provides a number of different tools and utilities that can be used to export a database, and the specific method you use will depend on your requirements and preferences.

Read full article
How To Install MySQL on Ubuntu
How To Install MySQL on Ubuntu

Installing MySQL on Ubuntu is a straightforward process that can be easily accomplished using the apt package manager. Ubuntu is a popular Linux operating system that is often used for web development, and it comes with a built-in package manager that makes it easy to install and manage software packages.

Read full article
How to create database in MySql ?
How to create database in MySql ?

To create a database in MySQL, you can use the following steps:
Open a MySQL command prompt. This can be done by logging in to your MySQL server and running the mysql command.
Use the CREATE DATABASE statement to create a new database.

Read full article
What is Sql | Examples of common SQL queries
What is Sql | Examples of common SQL queries

SQL (Structured Query Language) is a programming language used for managing and accessing data stored in relational databases. It is a standard language for working with relational databases, and it is used by many popular database management systems, such as MySQL, Oracle, and Microsoft SQL Server.

Read full article