A CROSS JOIN is like an INNER JOIN without the ON clause. It winds up producing results that are like multiplying each table with the other table. This is also referred to as a Cartesian result. A CROSS JOIN will return a combination of every row from two tables. This join will result in a lot of rows returned. It may result in your query never returning results because it's too intensive for the database system to return the results. 

To CROSS JOIN two tables, use the following syntax: 

SELECT column(s)
FROM table1
CROSS JOIN table2
WHERE condition(s);

The previous syntax shows you how to join two tables together with a CROSS JOIN. You join a column in table1 that matches a column in table2.

The following example will help you to understand how to use CROSS JOIN: 

SELECT c.playerid, c.schoolid, c.yearid, city, state, country 
FROM lahmansbaseballdb.collegeplaying c 

CROSS JOIN lahmansbaseballdb.schools s 
WHERE s.schoolid = 'akron';

The previous query gives you results of every row in collegeplaying that matches every row in schools, so it gives you the Cartesian product of those tables, as in the following screenshot:

As you can see in the preceding screenshot, the schoolid, city, state, and country fields from the schools table was placed in every row from the collegeplaying table, whereas the schoolid from the school table was equal to akron. Not a useful query in this case, but this is just done to illustrate how the cross joins work. This type of join could be useful for some queries—for example, if you want to know all the colors with sizes of a clothing item.

Also, you can use just the word JOIN, and leave off CROSS, to return the same results. This following query will return the same results as the query earlier in this section: 

SELECT c.playerid, c.schoolid, c.yearid, city, state, country 
FROM lahmansbaseballdb.collegeplaying c 
JOIN lahmansbaseballdb.schools s 
WHERE s.schoolid = 'akron';

The main thing to keep in mind with cross joins is that they can be very intensive for the database system, and it's best to use the other joins discussed earlier in this chapter if possible, and always use a WHERE clause if you are using a CROSS JOIN.