Here we will learn sqlite cross join with example and how to use sqlite cross join with multiple tables to get a Cartesian product of records with example.
In SQLite, CROSS JOIN is used to get the Cartesian product of rows by matching each row of the first table with every row of the second table.
By using the CROSS JOIN keyword in SQLite statements we can get the result which contains a combination of all the rows from the first table with all the rows of the second table.
In SQLite Cross join resultant table will contain multiplication number of rows in input tables. Suppose if table1 contains 10 rows and table2 contains 5 rows then if we apply Cross Join on these two tables we will get a resultant table that contains 50 rows.
Following is the syntax of using SQLite Cross Join with Select statement.
SELECT * FROM table1 CROSS JOIN table2
If you observe above SQLite Cross Join syntax we used CROSS JOIN keyword with tables table1, table2 to get Cartesian product result.
Following is the simple example of SQLite Cross Join with Select statement.
SELECT * FROM X CROSS JOIN Y;
In the above example, we applied CROSS JOIN on X and Y tables. The CROSS JOIN matches all the rows of X table with all the rows in Y table.
Following is the simple pictorial representation of SQLite CROSS JOIN.
Like as shown in the above diagram SQLite Cross Join will map all the rows of table X with all the rows of Y table and produce a Cartesian product of rows.
Now we will see the example of SQLite Cross JOIN with database table. We need to create two tables dept_master and emp_master and need to insert some data for that use following queries.
To create and insert some data in dept_master table execute following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control'),
('Marketing');
Once we create and insert data execute the following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute following queries to create and insert some data in emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,3),
(9,'Shwets','Jariwala',19400,2);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35000 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 49900 3
9 Shwets Jariwala 19400 2
Now write SQLite query like as shown following to use Cross JOIN with a select statement.
SELECT dept_name,emp_id,first_name FROM dept_master CROSS JOIN emp_master;
When we run above SQLite Cross Join query we will get a result like as shown below.
dept_name emp_id first_na
---------- ---------- --------
Admin 2 Shweta
Admin 3 Vinay
Admin 4 Jagruti
Admin 5 Shweta
Admin 6 Sonal
Admin 7 Yamini
Admin 8 Khyati
Admin 9 Shwets
Sales 2 Shweta
Sales 3 Vinay
Sales 4 Jagruti
Sales 5 Shweta
Sales 6 Sonal
Sales 7 Yamini
Sales 8 Khyati
Sales 9 Shwets
Quality Co 2 Shweta
Quality Co 3 Vinay
Quality Co 4 Jagruti
Quality Co 5 Shweta
Quality Co 6 Sonal
Quality Co 7 Yamini
Quality Co 8 Khyati
Quality Co 9 Shwets
Marketing 2 Shweta
Marketing 3 Vinay
Marketing 4 Jagruti
Marketing 5 Shweta
Marketing 6 Sonal
Marketing 7 Yamini
Marketing 8 Khyati
Marketing 9 Shwets
If you observe above result all the rows of dept_master table matched with all the row of emp_master and returned Cartesian product of dept_master and emp_master tables.
This is how we can use Cross Join in SQLite statements to get data based on our requirements.