mysql join simple example for beginners

SQL join are explained by Venn diagrams. Venn diagrams are notation which try to simplify the join operation and make it easy to be understand and remembered. For experienced person they can be easy but for beginners they could be complicated.

You can read more about Venn diagrams here: Venn diagram

Non Venn diagram SQL joins: MySQL joins non Venn diagram

Example data

Lets have two tables:

• boys
• girls

and if we want to know what are the relations between this two groups we should use joins:

boys

girls

(INNER) JOIN Get all couples

So if we want to get which people are in a couple and what are the couples then we need to use inner join:

SELECT *
FROM aboys A 
INNER JOIN agirls B 
ON A.relation = B.id

or get only the names:

SELECT A.name, B.name
FROM aboys A 
INNER JOIN agirls B 
ON A.relation = B.id

Left (OUTER) JOIN Get all boys with related girls

If you want to see all boys no matter are they in relation or not and all girls which are in relation you should use boys left join on girls

SELECT A.name, B.name
FROM aboys A 
LEFT OUTER JOIN agirls B 
ON A.relation = B.id

The result

Left Excluding JOIN Get all boys without a relation

This is the one that I'm using most in my practice. When you need to get all records from table A without matching record in table B. In this example we want to get all boys which are not in relation:

SELECT A.name, B.name
FROM aboys A 
LEFT OUTER JOIN agirls B 
ON A.relation = B.id
WHERE B.id IS NULL

result

Right (OUTER) JOIN Get all girls with related boys

Getting all girls and only boys which has relation with a girls

SELECT A.name, B.name
FROM aboys A 
Right OUTER JOIN agirls B 
ON A.relation = B.id

The result

FULL (OUTER) JOIN Return all rows

The full outer join return all records when there is a match in agirls or aboys tables. In mysql there isn't implementation of full outer join so you need to simulated by union of left and right joins:

SELECT *
FROM aboys A 
FULL OUTER JOIN agirls B 
ON A.relation = B.id

simulated mysql full outer join

SELECT A.name, B.name
FROM aboys A 
LEFT OUTER JOIN agirls B 
ON A.relation = B.id

UNION

SELECT A.name, B.name
FROM aboys A 
Right OUTER JOIN agirls B 
ON A.relation = B.id

SQL create table

CREATE TABLE `agirls` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

CREATE TABLE `aboys` (
	`id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`relation` INT(11) NULL DEFAULT NULL,
	INDEX `FK_aboys_agirls` (`relation`),
	CONSTRAINT `FK_aboys_agirls` FOREIGN KEY (`relation`) REFERENCES `agirls` (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

INSERT INTO `aboys` (`id`, `name`, `relation`) VALUES (1, 'Brady', 5);
INSERT INTO `aboys` (`id`, `name`, `relation`) VALUES (2, 'Mike', 1);
INSERT INTO `aboys` (`id`, `name`, `relation`) VALUES (3, 'Alex', 3);
INSERT INTO `aboys` (`id`, `name`, `relation`) VALUES (4, 'Daniel', NULL);
INSERT INTO `aboys` (`id`, `name`, `relation`) VALUES (5, 'James', NULL);

INSERT INTO `agirls` (`id`, `name`) VALUES (1, 'Emma');
INSERT INTO `agirls` (`id`, `name`) VALUES (2, 'Ann');
INSERT INTO `agirls` (`id`, `name`) VALUES (3, 'Kim');
INSERT INTO `agirls` (`id`, `name`) VALUES (4, 'Olivia');
INSERT INTO `agirls` (`id`, `name`) VALUES (5, 'Victoria');