- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - Cross Join
An SQL Cross Join is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables. That means, this join will combine each row of the first table with each row of second table (i.e. permutations).
A Cartesian product, or a cross product, is the result achieved from multiplication of two sets. This is done by multiplying all the possible pairs from both the sets.
The sample figure below illustrates the cross join in a simple manner.
As you can see, we considered two table columns: Hair Style and Hair Type. Each of these columns contain some records that need to be matched. Hence, using cross join, we combine each record in the “Hair Style” column with all records in the “Hair Type” column. The resultant table obtained is considered as the Cartesian product or Joined table.
Syntax
Following is the basic syntax of the Cross Join query in SQL −
SELECT column_name(s) FROM table1 CROSS JOIN table2;
Example
Let us consider two existing tables in an existing database, and try to join them using the cross join query as follows −
Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc., using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
The table will be created as −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | Kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Let us create another table Orders, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), );
Using the INSERT statement, insert values into this table as follows −
INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (102, '2009-10-08 00:00:00', 3, 3000.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (100, '2009-10-08 00:00:00', 3, 1500.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (101, '2009-11-20 00:00:00', 2, 1560.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (103, '2008-05-20 00:00:00', 4, 2060.00);
The table is displayed as follows −
+-----+---------------------+-------------+---------+ | OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+---------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000.00 | | 100 | 2009-10-08 00:00:00 | 3 | 1500.00 | | 101 | 2009-11-20 00:00:00 | 2 | 1560.00 | | 103 | 2008-05-20 00:00:00 | 4 | 2060.00 | +-----+---------------------+-------------+---------+
Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in Customers table with each row in Orders table.
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS;
Output
The resultant table is as follows −
+----+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 1 | Ramesh | 3000 | 2009-10-08 00:00:00 | | 1 | Ramesh | 1500 | 2009-10-08 00:00:00 | | 1 | Ramesh | 1560 | 2009-11-20 00:00:00 | | 1 | Ramesh | 2060 | 2008-05-20 00:00:00 | | 2 | Khilan | 3000 | 2009-10-08 00:00:00 | | 2 | Khilan | 1500 | 2009-10-08 00:00:00 | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 2 | Khilan | 2060 | 2008-05-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 3 | kaushik | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik | 2060 | 2008-05-20 00:00:00 | | 4 | Chaitali | 3000 | 2009-10-08 00:00:00 | | 4 | Chaitali | 1500 | 2009-10-08 00:00:00 | | 4 | Chaitali | 1560 | 2009-11-20 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | 3000 | 2009-10-08 00:00:00 | | 5 | Hardik | 1500 | 2009-10-08 00:00:00 | | 5 | Hardik | 1560 | 2009-11-20 00:00:00 | | 5 | Hardik | 2060 | 2008-05-20 00:00:00 | | 6 | Komal | 3000 | 2009-10-08 00:00:00 | | 6 | Komal | 1500 | 2009-10-08 00:00:00 | | 6 | Komal | 1560 | 2009-11-20 00:00:00 | | 6 | Komal | 2060 | 2008-05-20 00:00:00 | | 7 | Muffy | 3000 | 2009-10-08 00:00:00 | | 7 | Muffy | 1500 | 2009-10-08 00:00:00 | | 7 | Muffy | 1560 | 2009-11-20 00:00:00 | | 7 | Muffy | 2060 | 2008-05-20 00:00:00 | +----+----------+--------+---------------------+
Joining Multiple Tables with Cross Join
We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables.
Syntax
Following is the syntax to join multiple tables using cross join in SQL −
SELECT column_name(s) FROM table1 CROSS JOIN table2 CROSS JOIN table3 CROSS JOIN table4 . . .
Example
Let us try to combine three tables Customers, Orders and Order_Range, to demonstrate combining multiple tables using cross join.
We will try to create the Order_Range table using the query below −
CREATE TABLE ORDER_RANGE ( SNO INT NOT NULL, ORDER_RANGE VARCHAR (20) NOT NULL, );
Now, we can insert values into this empty tables using the INSERT statement as follows −
INSERT INTO ORDER_RANGE VALUES (1, '1-100'); INSERT INTO ORDER_RANGE VALUES (2, '100-200'); INSERT INTO ORDER_RANGE VALUES (3, '200-300');
The Order_Range table is as follows −
+-----+-------------+ | SNO | ORDER_RANGE | +-----+-------------+ | 1 | 1-100 | | 2 | 100-200 | | 3 | 200-300 | +-----+-------------+
Now we use the following cross join query on the given tables,
SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE FROM CUSTOMERS CROSS JOIN ORDERS CROSS JOIN ORDER_RANGE;
Output
The resultant table is given below −
+----+----------+---------+---------------------+-------------+ | ID | NAME | AMOUNT | DATE | ORDER_RANGE | +----+----------+---------+---------------------+-------------+ | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 4 | Chaitali | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 4 | Chaitali | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 4 | Chaitali | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 4 | Chaitali | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 4 | Chaitali | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 4 | Chaitali | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 4 | Chaitali | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 4 | Chaitali | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 4 | Chaitali | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 4 | Chaitali | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 200-300 | +----+----------+---------+---------------------+-------------+
Advertisements