CREATE TABLE members (
member_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (member_id)
);
CREATE TABLE committees (
committee_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (committee_id)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert some rows into the tables members
and committees
:
INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
mysql> SELECT * FROM members;
+-----------+--------+
| member_id | name |
+-----------+--------+
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM committees;
+--------------+--------+
| committee_id | name |
+--------------+--------+
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
+--------------+--------+
4 rows in set (0.00 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m inner join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 3 | 2 | Mary | Mary |
| 5 | 3 | Amelia | Amelia |
+-----------+--------------+--------+--------+
3 rows in set (0.01 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m left join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 2 | NULL | Jane | NULL |
| 3 | 2 | Mary | Mary |
| 4 | NULL | David | NULL |
| 5 | 3 | Amelia | Amelia |
+-----------+--------------+--------+--------+
5 rows in set (0.00 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m right join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 3 | 2 | Mary | Mary |
| 5 | 3 | Amelia | Amelia |
| NULL | 4 | NULL | Joe |
+-----------+--------------+--------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM members where name not in(SELECT name FROM committees);
+-----------+-------+
| member_id | name |
+-----------+-------+
| 2 | Jane |
| 4 | David |
+-----------+-------+
2 rows in set (0.01 sec)
mysql> select * from committees where name not in ( select name from members);
+--------------+------+
| committee_id | name |
+--------------+------+
| 4 | Joe |
+--------------+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)