Understand JOINS in RDBMS

Also check:- Best SEO Tips

“JOIN” is a SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.

Related Tables

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database will provide a number of tables containing related data. A very simple example would be users (students) and course enrollments:

‘user’ table:

MySQL table creation code:

CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The course number relates to a subject being taken in a course table…

‘course’ table:

id	Name
1	HTML5
2	CSS3
3	JavaScript
4	PHP
5	MySQL

MySQL table creation code:

CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Since we’re using InnoDB tables and know that user.course and course.id are related, we can specify a foreign key relationship:

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

In essence, MySQL will automatically:

• re-number the associated entries in the user.course column if the course.id changes
• reject any attempt to delete a course where users are enrolled.

important: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.

INNER JOIN (or just JOIN)

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

user.name	course.name
Alice	HTML5
Bob	HTML5
Carline	CSS3
David	MySQL

LEFT JOIN

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;

Result:

user.name	course.name
Alice	HTML5
Bob	HTML5
Carline	CSS3
David	MySQL
Emma	(NULL)

RIGHT JOIN

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name	course.name
Alice	HTML5
Bob	HTML5
Carline	CSS3
(NULL)	JavaScript
(NULL)	PHP
David	MySQL

SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;

We could, for example, count the number of students enrolled on each course:

SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;

Result:

course.name	count()
HTML5	2
CSS3	1
JavaScript	0
PHP	0
MySQL	1

OUTER JOIN (or FULL OUTER JOIN)

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id
UNION
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name	course.name
Alice	HTML5
Bob	HTML5
Carline	CSS3
David	MySQL
Emma	(NULL)
(NULL)	JavaScript
(NULL)	PHP