MySQL Joins

Last Updated: March 12, 2022

You can join two or more tables and show the rows of those tables

If you have two table names called X and Y

If you run the following SQL query what will be the results you are getting

CREATE TABLE .`X` (
  `id` INT NOT NULL,
  `A` VARCHAR(45) NULL,
  `B` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE .`Y` (
  `id` INT NOT NULL,
  `C` VARCHAR(45) NULL,
  `D` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

INSERT INTO `X` (`A`, `B`) VALUES ('a1', 'b1');
INSERT INTO `X` (`A`, `B`) VALUES ('a2', 'b2');
INSERT INTO `Y` (`C`, `D`) VALUES ('c1', 'd1');
INSERT INTO `Y` (`C`, `D`) VALUES ('c2', 'd2');

If you run the following SQL query what will be the results you are getting?

SELECT * from X,Y

You will have the following results

catesian product

This shows the rows from two tables and we call this a cartesian product.

Since there is no join condition in this SQL query you will get the above result set.

But if you run the following SQL query with JOIN condition you will end up with a different result set

SELECT * FROM X JOIN Y ON X.id=Y.id
join

MySQL JOIN types

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN
Test