CARTESIAN JOIN in SQL

CARTESIAN JOIN or CROSS JOIN in SQL used to combine two tables, each record of table 1 is combined with all records of table 2, forming a Cartesian product between the two tables with the result returned by the product of the number of records in two tables. Therefore, it is equivalent to an INNER JOIN when the set of data always satisfies the general condition from two tables or general conditions absent from the command.

1. JOIN clause in SQL

Syntax CARTESIAN JOIN in SQL

Basic syntax of CARTESIAN JOIN or CROSS JOIN as follows:

 SELECT cot1, cot2,. cotn 
 FROM bang1, bang2 

Parameters :

1. cot1, cot2, . cotn : the names of the columns to display in the query results. Cots are separated by commas (,)
2. bang1, bang2 : table names to retrieve data when querying.

Example of CARTESIAN JOIN in SQL

Suppose the two tables are NHANVIEN and TIENTHUONG with the following records:

Table 1: NHANVIEN

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

Table 2: TIENTHUONG

 +-----+---------------------+-------------+--------+ |TT_ID| NGAY | NHANVIEN_ID | SOTIEN | +-----+---------------------+-------------+--------+ | 102 | 2019-01-08 00:00:00 | 3 | 3000 | | 100 | 2019-01-08 00:00:00 | 3 | 1500 | | 101 | 2019-02-20 00:00:00 | 2 | 1560 | | 103 | 2018-12-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ 

Now, let's join these two tables using INNER JOIN as follows:

 SQL> SELECT ID, TEN, SOTIEN, NGAY 
 FROM NHANVIEN, TIENTHUONG; 

The result is:

 +----+----------+--------+---------------------+ | ID | TEN | SOTIEN | NGAY | +----+----------+--------+---------------------+ | 1 | Thanh | 3000 | 2019-01-08 00:00:00 | | 1 | Thanh | 1500 | 2019-01-08 00:00:00 | | 1 | Thanh | 1560 | 2019-02-20 00:00:00 | | 1 | Thanh | 2060 | 2018-12-20 00:00:00 | | 2 | Loan | 3000 | 2019-01-08 00:00:00 | | 2 | Loan | 1500 | 2019-01-08 00:00:00 | | 2 | Loan | 1560 | 2019-02-20 00:00:00 | | 2 | Loan | 2060 | 2018-12-20 00:00:00 | | 3 | Nga | 3000 | 2019-01-08 00:00:00 | | 3 | Nga | 1500 | 2019-01-08 00:00:00 | | 3 | Nga | 1560 | 2019-02-20 00:00:00 | | 3 | Nga | 2060 | 2018-12-20 00:00:00 | | 4 | Manh | 3000 | 2019-01-08 00:00:00 | | 4 | Manh | 1500 | 2019-01-08 00:00:00 | | 4 | Manh | 1560 | 2019-02-20 00:00:00 | | 4 | Manh | 2060 | 2018-12-20 00:00:00 | | 5 | Huy | 3000 | 2019-01-08 00:00:00 | | 5 | Huy | 1500 | 2019-01-08 00:00:00 | | 5 | Huy | 1560 | 2019-02-20 00:00:00 | | 5 | Huy | 2060 | 2018-12-20 00:00:00 | | 6 | Cao | 3000 | 2019-01-08 00:00:00 | | 6 | Cao | 1500 | 2019-01-08 00:00:00 | | 6 | Cao | 1560 | 2019-02-20 00:00:00 | | 6 | Cao | 2060 | 2018-12-20 00:00:00 | | 7 | Lam | 3000 | 2019-01-08 00:00:00 | | 7 | Lam | 1500 | 2019-01-08 00:00:00 | | 7 | Lam | 1560 | 2019-02-20 00:00:00 | | 7 | Lam | 2060 | 2018-12-20 00:00:00 | +----+----------+--------+---------------------+ 

See more types of JOIN:

1. INNER JOIN - Returns records with matching values ​​between two tables.
2. LEFT JOIN - Returns all records from the left panel and matching records from the right panel.
3. RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
4. FULL JOIN - Returns all records in the left panel and the table must be combined.
5. SELF JOIN - Combine a table with itself as if the table is considered two tables, change the temporary name at least one table in the SQL command.