'USING' command in LEFT JOIN

  

mysql> SELECT ArticleTitle, Copyright, AuthID
    -> FROM Articles LEFT JOIN AuthorArticle
    ->    USING (ArticleID)
    -> ORDER BY ArticleTitle;
+-------------------+-----------+--------+
| ArticleTitle      | Copyright | AuthID |
+-------------------+-----------+--------+
| AI                |      1993 |   1012 |
| Buy a paper       |      1932 |   1008 |
| Buy a paper       |      1932 |   1011 |
| Conferences       |      1996 |   1014 |
| How write a paper |      1934 |   1009 |
| Information       |      1992 |   1012 |
| Journal           |      1980 |   1010 |
| Publish a paper   |      1919 |   NULL |
| Sell a paper      |      1966 |   1006 |
+-------------------+-----------+--------+
9 rows in set (0.00 sec)



Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;


INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;


INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);
  
SELECT ArticleTitle, Copyright, AuthID
FROM Articles LEFT JOIN AuthorArticle
   USING (ArticleID)
ORDER BY ArticleTitle;

Related examples in the same category

1.	LEFT JOIN tables
2.	Two LEFT JOIN in select command
3.	Two LEFT JOIN
4.	Creating the table list with LEFT JOIN and then forming the linking connection with ON
5.	A LEFT JOIN and a regular join
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

'USING' command in LEFT JOIN : Left Join « Join « SQL / MySQL

Related examples in the same category