Home     SQL Basic     MYSQL Basic     Contact Us     Site Map    

SQL BASIC

SQL SELECT

SQL WHERE

SQL DISTINCT

SQL AND

SQL OR

SQL NOT

SQL ORDER BY

SQL IN

SQL EXISTS

SQL BETWEEN

SQL LIKE

SQL ALIAS

SQL AGGREGATE

>SQL COUNT

>SQL SUM

>SQL MAX

>SQL MIN

>SQL AVG

SQL GROUP BY

SQL HAVING

SQL INSERT

SQL UPDATE

SQL DELETE

SQL SELECT INTO

SQL CREATE DATABASE

SQL CREATE TABLE

SQL DROP TABLE

SQL DROP DATABASE

SQL CREATE INDEX

SQL PRIMARY KEY

SQL FOREIGN KEY

SQL ALTER TABLE

SQL TRUNCATE TABLE

SQL JOIN

SQL INNER JOIN

SQL OUTER JOIN

SQL CROSS JOIN

SQL UNION

SQL UNION ALL

SQL INTERSECT



SQL CROSS JOIN

SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

OR

SELECT * FROM [TABLE 1], [TABLE 2]


EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerNameDepartmentIdScores
Jason13000
Irene11500
Jane21000
David22500
Paul32000
James32000

Table 2: Departments

DepartmentIdDepartmentName
1IT
2Marketing
3HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerNameDepartmentIdScoresDepartmentIdDepartmentName
Jason130001IT
Irene115001IT
Jane210001IT
David225001IT
Paul320001IT
James320001IT
Jason130002Marketing
Irene115002Marketing
Jane210002Marketing
David225002Marketing
Paul320002Marketing
James330002Marketing
Jason130003HR
Irene115003HR
Jane210003HR
David225003HR
Paul320003HR
James330003HR