MySQL JOIN Clauses (1/4) – The Cross JOIN

In this short article series I would like to describe the meaning of MySQL Join clauses. MySQL semantics is similar among different SQL database engines, so this knowledge could be proudly used in other SQL environments. Have a nice reading in part one of four.

But why use JOINS?

Joins help collecting or getting data from two, three or more database tables. Usually, the tables are mutually related using primary or foreign keys. For the better understanding and simplicity as well, we will be using two simple databases created especially for this. In this article we’ll focus on the Cross JOIN clause, below you can find two table schemas with some example data. The first table is called members and the second is movies.

The members and movies tables

First what we need is to create two databases and feed them with some example data. To do that, execute the following queries:

CREATE TABLE `members` ( `id` INT NOT NULL AUTO_INCREMENT , `first_name` VARCHAR(150) NOT NULL , `last_name` VARCHAR(150) NOT NULL , `movie_id` INT NOT NULL , PRIMARY KEY (`id`));

INSERT INTO `members` (`id`, `first_name`, `last_name`, `movie_id`) VALUES ('1', 'Cezary', 'Kaszuba', '1'), ('2', 'Tomasz', 'Skawinsky', '2'), ('3', 'Adam', 'Freeman', '3'), ('4', 'Gabriel', 'Piston', '5'), ('5', 'Susan', 'Adelheim', '5'), ('6', 'Jenny', 'Osborne', '8'), ('7', 'Lee', 'Tong', '10')

CREATE TABLE `movies` ( `id` INT NOT NULL AUTO_INCREMENT , `title` VARCHAR(150) NOT NULL , `category` VARCHAR(150) NOT NULL , PRIMARY KEY (`id`));

INSERT INTO `movies` (`id`, `title`, `category`) VALUES ('7', 'Joker ', 'Thriller '), ('8', 'Alien ', 'Horror'), ('9', 'Django Unchained', 'Western '), ('10', 'Star Wars: Episode V - The Empire Strikes Back', 'Sci-Fi'), ('1', 'The Shawshank Redemption', 'Drama'), ('2', 'The Godfather', 'Crime'), ('3', 'The Dark Knight', 'Action'), ('4', 'Pulp Fiction', 'Crime'), ('5', 'The Matrix', 'Sci-Fi'), ('6', 'Interstellar ', 'Sci-Fi')

Cross JOIN

Cross JOIN is the simplest form of all JOIN clauses. When we use it, matches each row from one database table to all rows of another, second table. In simplify, The Cross JOIN gives us combinations of each row of first table with all records included in second. Suppose that we want to get all member records towards all the movie records. To do that, we can use the script below to get our desired results:

SELECT * FROM `movies` CROSS JOIN `members` LIMIT 30;

Executing the above script in MySQL console, we should see the following result:

For this example I set the limit to 30 rows, of course it would be greatly more. Try it yourself removing the limit. See also the second part of this article series, where I describe The Inner JOIN clause.

Also go to the second article where you can read about the Inner JOIN.