SQL Cross Join Cartesian product Examples

Updated on 07 Feb 2012,
Published on 28 Feb 2008

SQL Cross join returns the output result as a Cartesian product of both database tables.

Let left table has 10 rows and right table has 8 rows then SQL CROSS Join will return 180 rows combining each record of left table with all records of right side table. Consider the following example of CROSS Join:

USE PUBS

SELECT		AU_FNAME, 
			AU_LNAME, 
			PUB_NAME
FROM		AUTHORS 
CROSS JOIN	PUBLISHERS 
ORDER BY	AU_FNAME

Above cross join will return 23 * 8 = 184 results by multiplying each row of authors table with publishers table.

SQL CROSS Join with WHERE clause

By just adding the where clause with Cross join sql query it turns the output result into inner join.

Example

USE PUBS

SELECT		AU_FNAME, 
			AU_LNAME, PUB_NAME
FROM		AUTHORS 
CROSS JOIN	PUBLISHERS 
WHERE		AUTHORS.CITY = PUBLISHERS.CITY
ORDER BY	AU_FNAME

It will display only the matching results in both tables.

Result

au_fname	au_lname	pub_name
Cheryl	Carson	Algodata Infosystems
Abraham	Bennet	Algodata Infosystems

Continue to next tutorial: Types of Joins in Sql Server 2005 to learn about the different types joins which can be used to join the sql server tables to get the relational fields in the output result.

Previous Tutorial

Next Tutorial

sponsored links

6 Responses to "SQL Cross Join Cartesian product Examples"

Gohar

Hello, I have a table on SQL-Server 2005 named table_one. This table has 6 index keys (indx_1,indx_2,...indx_6 )on server with different definitions. Would you please say me, how can i read from my (visual foxpro) programm the index key definitions of this table (table_one). I use this table in a cursor adapter..... In other words i open the table in my programm and want to see all of the indexes with their definitions. Would you please help me? Many thanks in advance ....

RAPIST RANJITH

CROSS JOIN will do A * B rows..ex 10 * 8 = 80 rows

ravi

can u tell me, how come the following result, 1. Two table table 1 and table 2 Tab 1 and Tab2 and This table has two column col1 col2 col1 col2 1 2 2 3 10 4 5 3 4 12 5 6 8 9 8 7 how to write the query to get the below result col1 col2 1 2 2 3 10 3 4 4 5 5 6 8 reply as soon as possible, please...

Ezineasp.net

Hi Ravi

Are u trying to merge col1 of tab1 with col1 of tab2
and col2 of tabl with col2 of tab2?
Please explain it?

ravi

table 1: ---------- col1 col2 ---------- 1 2 10 3 4 5 6 8 ---------- table 2: ---------- col2 col2 ---------- 2 3 4 5 12 8 9 7 ---------- The result table should be in the form of ----------- col1 col2 ----------- 1 2 2 3 10 3 4 4 5 5 6 8 ----------- and i want to make sure that, i am not merging the columns of two tables. And also kindly forgive me that, i have not explained with this question anything more. but as far as i understand, it just have to join the two rows or records and arrange it in ascending order. yes, we can do it with the help of join functions, and orderby. but somehow the row2,row5 of table1 are not sorted. --------------------------------------------------- i am a beginner, i am in the basic levels only. i have used all the join function. But i am not getting the desired output. if i am right, definitely the joining should be done with some queries in it for getting this result. i think i got the logic but am not sure it could be applicable. My idea is, i have to select the records one by one from the table1. suppose in case if i find the(col2 of table1) matches with the any of the column1 of table2 then that table2 record must be inserted next. otherwise it should simply add the next record in the table 1. i this is the process goes on the result table. But i am not sure. There could be another way. or my logic could be wrong. in case if it is right, can u provide me the code for adding those conditions in the joining functions. or any other way for getting the answer. Note: i am not sure that this is the right way to approach you, because i am just the beginner. Thank you for your response.

Ezineasp.net

Hello Ravi

Try the following SQL code if its the same you are looking for:

If (OBJECT_ID('tempdb..#temp_Table')) IS NOT NULL
DROP TABLE #temp_Table

CREATE TABLE #temp_Table
(
colId int identity(1,2),
col1 int,
col2 int
)

INSERT INTO #temp_Table(col1, col2)
SELECT col1,col2 FROM Table_1

DBCC CHECKIDENT (#temp_Table, RESEED, 0)

INSERT INTO #temp_Table(col1, col2)
SELECT col1, col2 FROM Table_2

SELECT col1, col2 FROM #temp_Table ORDER BY colId

DROP TABLE #temp_Table

Hope it will help you. Good Luck ;)