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

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Join » Left Join 
'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
java2s.com  |  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.