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)

Related examples in the same category

1.	Using More Than one Table
2.	Self join
3.	Simple table join
4.	Join three tables
5.	Query data from two tables
6.	JOIN two tables with alias name
7.	Using a Join to Control Query Output Order
8.	Using a Join to Create a Lookup Table from Descriptive Labels
9.	Return the first names and surnames of both the sales rep and the customer, as well as the value of the sale
10.	Query data from two tables 2
11.	Finding Rows in One Table That Match Rows in Another
12.	Identify records from author table that corresponds to the author name, use its a_id value to find matching re
13.	Using information in the book table to find information in the author table
14.	Shorten the output column list to include only columns from the author table
15.	List each author from the author table, and whether or not you have any books by the author
16.	Using table alias to qualify column name when column names exist
17.	Using table alias to qualify column name
18.	PSEUDONYMS FOR TABLE NAMES
19.	Qualify the column name with table name

Finding Rows with No Match in Another Table : Simple JOIN « Join « SQL / MySQL

Related examples in the same category