Cross-connect (CROSS join)

9.3.5 Cross Join (CROSS join)

In addition to using a comma-separated table in the FROM clause, SQL supports another operation called a cross join. They all return the Cartesian product of all data rows of the connected two tables, and the number of rows returned is equal to the number of data rows in the first table that match the query criteria multiplied by the number of rows of data in the second table that match the query criteria. The only difference is that the cross join keyword instead of a comma is used when the cross joins separate column names.

In fact, the following two expressions are completely equivalent.

SELECT * FROM table1, table2 SELECT * FROM table1 CROSS JOIN table2

Figure 9.25 shows a typical cross link schematic.

Figure 9.25 Cross-connect

Instance 15 cross-connect Student table and course table

Cross-connect Student table and course table, query all students ' number, name, course code, course name, test time and score information. Instance code:

SELECT SNO, Sname, S.cno, C.cname, CTest, MARK From STUDENT as S CROSS JOIN COURSE as C WHERE S. CNO = C. CNO and mark>=60

The results of the operation are shown in Figure 9.26.

Note When you use the Cross join keyword to cross-connect a table, you cannot use the ON keyword and can only define search conditions in the WHERE clause, because the Cartesian product of two tables is generated.

In fact, the direct use of a cross join rarely gets the desired result, but, as the example shows, as the first step in the query, the DBMS usually cross join the connected table in the FROM clause and then filter the resulting intermediate table.

Figure 9.26 Query results for cross-connect student tables and course tables