Finding Rows with No Match in Another Table
mysql> mysql> CREATE TABLE author -> ( -> a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # author ID -> name VARCHAR(30) NOT NULL, # author name -> PRIMARY KEY (a_id), -> UNIQUE (name) -> ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE book -> ( -> a_id INT UNSIGNED NOT NULL, # author ID -> p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # book ID -> title VARCHAR(100) NOT NULL, # title of book -> state VARCHAR(2) NOT NULL, # state where purchased -> price INT UNSIGNED, # purchase price (dollars) -> INDEX (a_id), -> PRIMARY KEY (p_id) -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO author (name) VALUES -> ('Tom'), -> ('Monet'), -> ('Jack'), -> ('Picasso'), -> ('Mary') -> ; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'Database', 'IN', 34 FROM author WHERE name = 'Tom'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'SQL', 'MI', 87 FROM author WHERE name = 'Tom'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'MySQL', 'KY', 48 FROM author WHERE name = 'Jack'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'XML', 'KY', 67 FROM author WHERE name = 'Jack'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'Java', 'IA', 33 FROM author WHERE name = 'Jack'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO book (a_id,title,state,price) -> SELECT a_id, 'HTML', 'NE', 64 FROM author WHERE name = 'Mary'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> mysql> SELECT * FROM author ORDER BY a_id; +------+---------+ | a_id | name | +------+---------+ | 1 | Tom | | 2 | Monet | | 3 | Jack | | 4 | Picasso | | 5 | Mary | +------+---------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM book ORDER BY a_id, p_id; +------+------+----------+-------+-------+ | a_id | p_id | title | state | price | +------+------+----------+-------+-------+ | 1 | 1 | Database | IN | 34 | | 1 | 2 | SQL | MI | 87 | | 3 | 3 | MySQL | KY | 48 | | 3 | 4 | XML | KY | 67 | | 3 | 5 | Java | IA | 33 | | 5 | 6 | HTML | NE | 64 | +------+------+----------+-------+-------+ 6 rows in set (0.00 sec) mysql> mysql> SELECT * FROM author, book WHERE author.a_id != book.a_id; +------+---------+------+------+----------+-------+-------+ | a_id | name | a_id | p_id | title | state | price | +------+---------+------+------+----------+-------+-------+ | 2 | Monet | 1 | 1 | Database | IN | 34 | | 3 | Jack | 1 | 1 | Database | IN | 34 | | 4 | Picasso | 1 | 1 | Database | IN | 34 | | 5 | Mary | 1 | 1 | Database | IN | 34 | | 2 | Monet | 1 | 2 | SQL | MI | 87 | | 3 | Jack | 1 | 2 | SQL | MI | 87 | | 4 | Picasso | 1 | 2 | SQL | MI | 87 | | 5 | Mary | 1 | 2 | SQL | MI | 87 | | 1 | Tom | 3 | 3 | MySQL | KY | 48 | | 2 | Monet | 3 | 3 | MySQL | KY | 48 | | 4 | Picasso | 3 | 3 | MySQL | KY | 48 | | 5 | Mary | 3 | 3 | MySQL | KY | 48 | | 1 | Tom | 3 | 4 | XML | KY | 67 | | 2 | Monet | 3 | 4 | XML | KY | 67 | | 4 | Picasso | 3 | 4 | XML | KY | 67 | | 5 | Mary | 3 | 4 | XML | KY | 67 | | 1 | Tom | 3 | 5 | Java | IA | 33 | | 2 | Monet | 3 | 5 | Java | IA | 33 | | 4 | Picasso | 3 | 5 | Java | IA | 33 | | 5 | Mary | 3 | 5 | Java | IA | 33 | | 1 | Tom | 5 | 6 | HTML | NE | 64 | | 2 | Monet | 5 | 6 | HTML | NE | 64 | | 3 | Jack | 5 | 6 | HTML | NE | 64 | | 4 | Picasso | 5 | 6 | HTML | NE | 64 | +------+---------+------+------+----------+-------+-------+ 24 rows in set (0.00 sec) mysql> mysql> drop table book; Query OK, 0 rows affected (0.00 sec) mysql> drop table author; Query OK, 0 rows affected (0.00 sec)