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:

Learn different types of Joins in SQL:

SQL Left Outer Join Examples

SQL Right Outer Join Examples

SQL Full Outer Join Examples

SQL Inner Join Examples