A LEFT JOIN and a regular join

     
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.00 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, book
    -> WHERE author.a_id = book.a_id;
+------+------+------+------+----------+-------+-------+
| a_id | name | a_id | p_id | title    | state | price |
+------+------+------+------+----------+-------+-------+
|    1 | Tom  |    1 |    1 | Database | IN    |    34 |
|    1 | Tom  |    1 |    2 | SQL      | MI    |    87 |
|    3 | Jack |    3 |    3 | MySQL    | KY    |    48 |
|    3 | Jack |    3 |    4 | XML      | KY    |    67 |
|    3 | Jack |    3 |    5 | Java     | IA    |    33 |
|    5 | Mary |    5 |    6 | HTML     | NE    |    64 |
+------+------+------+------+----------+-------+-------+
6 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM author LEFT JOIN book
    -> ON author.a_id = book.a_id;
+------+---------+------+------+----------+-------+-------+
| a_id | name    | a_id | p_id | title    | state | price |
+------+---------+------+------+----------+-------+-------+
|    1 | Tom     |    1 |    1 | Database | IN    |    34 |
|    1 | Tom     |    1 |    2 | SQL      | MI    |    87 |
|    2 | Monet   | NULL | NULL | NULL     | NULL  |  NULL |
|    3 | Jack    |    3 |    3 | MySQL    | KY    |    48 |
|    3 | Jack    |    3 |    4 | XML      | KY    |    67 |
|    3 | Jack    |    3 |    5 | Java     | IA    |    33 |
|    4 | Picasso | NULL | NULL | NULL     | NULL  |  NULL |
|    5 | Mary    |    5 |    6 | HTML     | NE    |    64 |
+------+---------+------+------+----------+-------+-------+
8 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.	LEFT JOIN tables
2.	'USING' command in LEFT JOIN
3.	Two LEFT JOIN in select command
4.	Two LEFT JOIN
5.	Creating the table list with LEFT JOIN and then forming the linking connection with ON
6.	To list each author record, whether or not there are any book records for it, use a LEFT JOIN
7.	To force each category to be displayed, use a reference table and a LEFT JOIN.
8.	Creating Left Joins
9.	Replacing the ON clause with the USING clause for Left Join
10.	Use a left join to link more than two tables.
11.	Addition of the LEFT keyword to each join definition
12.	Left Joins (Left Outer Joins)
13.	Performing a LEFT JOIN on just the customer and sales tables.
14.	Table order in a LEFT JOIN is important.
15.	Left outer join syntax
16.	Left outer join then order
17.	Left outer join
18.	Left outer join with using clause
19.	Left outer join with subquery
20.	Query from left outer join
21.	To return all the sales reps who have not yet made a sale

A LEFT JOIN and a regular join : Left Join « Join « SQL / MySQL

Related examples in the same category