SQL joins and a pinch of set operations

First Normal Form (1NF):

  • Data is stored in tables with rows uniquely identified by a primary key
  • Data within each table is stored in individual columns in its most reduced form
  • There are no repeating groups

Second Normal Form (2NF):

  • Everything from 1NF
  • Only data that relates to a table’s primary key is stored in each table

Third Normal Form (3NF):

  • Everything from 2NF
  • There are no in-table dependencies between the columns in each table
MySQL Architecture
  • The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.
  • A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
mysql -t < sakila-schema.sql
mysql -t < sakila-data.sql
grant usage on `sakila`.* TO ‘uadmin’ @localhost identified by "passwd";
grant usage on `sakila`.* TO ‘uadmin’ @ ‘%’ identified by "passwd";
grant all privileges on `sakila`.* TO ‘uadmin’ @localhost;
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)
show create table actor\G;
*************************** 1. row ***************************
Table: actorCreate Table: CREATE TABLE `actor` (`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,`first_name` varchar(45) NOT NULL,`last_name` varchar(45) NOT NULL,`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),PRIMARY KEY (`actor_id`),KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf81 row in set (0.000 sec)show create table film_actor\G;*************************** 1. row ***************************Table: film_actorCreate Table: CREATE TABLE `film_actor` (`actor_id` smallint(5) unsigned NOT NULL,`film_id` smallint(5) unsigned NOT NULL,`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),PRIMARY KEY (`actor_id`,`film_id`),KEY `idx_fk_film_id` (`film_id`),CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.000 sec)
show create table film_actor\G;*************************** 1. row ***************************Table: film_actorCreate Table: CREATE TABLE `film_actor` (`actor_id` smallint(5) unsigned NOT NULL,`film_id` smallint(5) unsigned NOT NULL,`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),PRIMARY KEY (`actor_id`,`film_id`),KEY `idx_fk_film_id` (`film_id`),CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.000 sec)REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.000 sec)

Joining SQL tables

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It’s similar with the intersection between 2 sets. The intersection of two sets A and B, denoted by A∩B, consists of all elements that are both in A and B. For example, {1,2,3,4}∩{2,3,5}={2,3}.

Set operators

The relational algebra uses set union, set difference, and Cartesian product from set theory, but adds additional constraints to these operators.

  • inner join/cross join
  • left join
  • right join
MariaDB [sakila]> select count(*) from actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.000 sec)
MariaDB [sakila]> select count(*) from actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.000 sec)
==================================================================MariaDB [sakila]> select * from actor join film_actor on actor.actor_id=film_actor.actor_id limit 10;
+----------+------------+-----------+---------------------+----------+---------+---------------------+
| actor_id | first_name | last_name | last_update | actor_id | film_id | last_update |
+----------+------------+-----------+---------------------+----------+---------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 1 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 23 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 25 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 106 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 140 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 166 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 277 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 361 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 438 | 2006-02-15 05:05:03 |
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 499 | 2006-02-15 05:05:03 |
+----------+------------+-----------+---------------------+----------+---------+---------------------+
10 rows in set (0.000 sec)
==================================================================MariaDB [sakila]> select count(*) from actor join film_actor on actor.actor_id=film_actor.actor_id limit 10;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+
1 row in set (0.000 sec)

Left Join

MariaDB [sakila]> select * from customer limit 2\G;
*************************** 1. row ***************************
customer_id: 1
store_id: 1
first_name: MARY
last_name: SMITH
email: MARY.SMITH@sakilacustomer.org
address_id: 5
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
*************************** 2. row ***************************
customer_id: 2
store_id: 1
first_name: PATRICIA
last_name: JOHNSON
email: PATRICIA.JOHNSON@sakilacustomer.org
address_id: 6
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
*************************** 3. row ***************************
MariaDB [sakila]> select * from actor limit 2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.000 sec)
MariaDB [sakila]> select count(*) from customer left join actor on customer.last_name=actor.last_name;
+----------+
| count(*) |
+----------+
| 620 |
+----------+
1 row in set (0.002 sec)
=================================================================MariaDB [sakila]> select count(*) from customer left join actor on customer.last_name=actor.last_name where actor_id!="NULL";
+----------+
| count(*) |
+----------+
| 43 |
+----------+
1 row in set, 3 warnings (0.002 sec)

Right join

select * from customer right join actor on customer.last_name=actor.last_name limit 4\G;
*************************** 1. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 1
first_name: PENELOPE
last_name: GUINESS
last_update: 2006-02-15 04:34:33
*************************** 2. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 2
first_name: NICK
last_name: WAHLBERG
last_update: 2006-02-15 04:34:33
*************************** 3. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 3
first_name: ED
last_name: CHASE
last_update: 2006-02-15 04:34:33
*************************** 4. row ***************************
customer_id: 6
store_id: 2
first_name: JENNIFER
last_name: DAVIS
email: JENNIFER.DAVIS@sakilacustomer.org
address_id: 10
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
actor_id: 4
first_name: JENNIFER
last_name: DAVIS
last_update: 2006-02-15 04:34:33
4 rows in set (0.001 sec)
MariaDB [sakila]> select count(*) from customer right join actor on customer.last_name=actor.last_name where customer.customer_id!="NULL";
+----------+
| count(*) |
+----------+
| 43 |
+----------+
1 row in set, 3 warnings (0.001 sec)
MariaDB [sakila]> select count(*) from customer right join actor on customer.last_name=actor.last_name;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.002 sec)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Mihai Pruna

Mihai Pruna

5 Followers

“All that is gold does not glitter, Not all those who wander are lost; The old that is strong does not wither, Deep roots are not reached by the frost”