join

 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)