SQLite Cross Join

  Total Views : 38
  Comments : 0
 
  Keywords : SQLite cross join with multiple tables example, SQLite cross join query with example, SQLite cross join to get Cartesian product of rows with example

Here we will learn sqlite cross join with example and how to use sqlite cross join with multiple tables to get Cartesian product of records with example.

SQLite Cross Join

In SQLite CROSS JOIN is used to get the Cartesian product of rows by matching each row of first table with every row of second table.

 

By using CROSS JOIN keyword in SQLite statements we can get the result which contains combination of all the rows from first table with all the rows of 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 resultant table which contains 50 rows.

Syntax of SQLite Cross Join

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. 

Example of SQLite Cross Join

Following is the simple example of SQLite Cross Join with Select statement.

 

SELECT FROM X CROSS JOIN Y;

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

 

SQLite Cross Join Example Pictorial Representation

 

Like as shown in above diagram SQLite Cross Join will map all the rows of table X with all the rows of Y table and produce Cartesian product of rows.

 

Now we will see the example of SQLite Cross JOIN with database table. We need 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 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 select statement.

 

SELECT dept_name,emp_id,first_name FROM dept_master CROSS JOIN emp_master;

Result of SQLite Cross Join Example

When we run above SQLite Cross Join query we will get 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.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User