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.

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

id name relation
3 Alex 3
4 Daniel
5 James
2 Mike 1

girls

id name
1 Emma
2 Ann
3 Kim
4 Olivia
5 Victoria

## (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
``````
name name
Mike Emma
Alex Kim

## 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

name name
Mike Emma
Alex Kim
Daniel
James

## 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

name name
Daniel
James

## 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

name name
Mike Emma
Ann
Alex Kim
Olivia

## 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
``````
name name
Mike Emma
Alex Kim
Daniel
James
Ann
Olivia

## 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');

``````

Python

Python

MySQL

MySQL

MySQL

Python

## Python read, validate and import CSV/JSON file to MySQL

You've successfully subscribed to SoftHints - Python, Data Science and Linux Tutorials
Great! Next, complete checkout for full access to SoftHints - Python, Data Science and Linux Tutorials
Welcome back! You've successfully signed in