SQL CROSS JOIN or CARTESIAN JOIN

It will return Cartesian Product of sets of rows from the 2 joined table. Here each row of the first table is combined with each row from the second table. Total number of rows present in the final output will be the product of number of rows present in both the tables.

For example PRODUCTS Table has:

product_id	product_name	supplier_id	category_id	quantity_per_unit	price
1	Aniseed Syrup	1	2	12 – 550 ml bottles	10
2	Cajun Seasoning	2	2	48 – 6 oz jars	22
3	Green Tea	1	1	10 boxes x 20 bags	18
4	Gumbo Mix	2	2	36 boxes	21.35
5	Grandmas Boysenberry Spread	3	2	12 – 8 oz jars	25
6	Uncle Bobs Organic Dried Pears	3	7	12 – 1 lb pkgs.	30
7	Northwoods Cranberry Sauce	3	2	12 – 12 oz jars	40
8	Mishi Kobe Niku	4	6	18 – 500 g pkgs.	97

SUPPLIERS Table has:

supplier_id	supplier_name	contact_name	address	city	postal_code	country	phone
1	Exotic Liquids	Charlotte Cooper	49 Gilbert St.	London	EC1 4SD	UK	(171) 555-2222
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
3	Grandma Kellys Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
4	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan	(03) 3555-5011
5	Mayumis	Mayumi Ohno	92 Setsuko Chuo-ku	Osaka	545	Japan	(06) 431-7877
6	Pavlova, Ltd.	Ian Devling	74 Rose St. Moonie Ponds	Melbourne	3058	Australia	(03) 444-2343

Lets execute this cross join query:

SELECT * FROM PRODUCTS CROSS JOIN SUPPLIERS

Its output will be like:

product_id	product_name	supplier_id	category_id	quantity_per_unit	price	supplier_name	contact_name	address	city	postal_code	country	phone
1	Aniseed Syrup	1	2	12 – 550 ml bottles	10	Exotic Liquids	Charlotte Cooper	49 Gilbert St.	London	EC1 4SD	UK	(171) 555-2222
1	Aniseed Syrup	2	2	12 – 550 ml bottles	10	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
1	Aniseed Syrup	3	2	12 – 550 ml bottles	10	Grandma Kellys Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
1	Aniseed Syrup	4	2	12 – 550 ml bottles	10	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan	(03) 3555-5011
1	Aniseed Syrup	5	2	12 – 550 ml bottles	10	Mayumis	Mayumi Ohno	92 Setsuko Chuo-ku	Osaka	545	Japan	(06) 431-7877
1	Aniseed Syrup	6	2	12 – 550 ml bottles	10	Pavlova, Ltd.	Ian Devling	74 Rose St. Moonie Ponds	Melbourne	3058	Australia	(03) 444-2343
1	Aniseed Syrup	7	2	12 – 550 ml bottles	10	Specialty Biscuits, Ltd.	Peter Wilson	29 Kings Way	Manchester	M14 GSD	UK	(161) 555-4448
1	Aniseed Syrup	8	2	12 – 550 ml bottles	10	PB Knckebrd AB	Lars Peterson	Kaloadagatan 13	Gteborg	S-345 67	Sweden	031-987 65 43
1	Aniseed Syrup	9	2	12 – 550 ml bottles	10	Refrescos Americanas LTDA	Carlos Diaz	Av. das Americanas 12.890	So Paulo	5442	Brazil	(11) 555 4640

Learn more : SQL Tutorial , SQL Interview Questions