Sub query with EXISTS command : Exists « Select Clause « 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 » Select Clause » Exists 




Sub query with EXISTS command
   
/*

mysql> SELECT ArticleID, ArticleTitle
    -> FROM Articles AS b
    -> WHERE EXISTS
    ->    (
    ->       SELECT ArticleID
    ->       FROM AuthorArticle AS ab
    ->       WHERE b.ArticleID=ab.ArticleID
    ->    )
    -> ORDER BY ArticleTitle;
+-----------+-------------------+
| ArticleID | ArticleTitle      |
+-----------+-------------------+
|     19354 | AI                |
|     15729 | Buy a paper       |
|     16284 | Conferences       |
|     12786 | How write a paper |
|     19264 | Information       |
|     17695 | Journal           |
|     14356 | Sell a paper      |
+-----------+-------------------+
7 rows in set (0.01 sec)


*/       
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60NOT 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 (AuthIDREFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleIDREFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (100614356)
                              (100815729)
                              (100912786)
                              (101017695),
                              (101115729)
                              (101219264)
                              (101219354)
                              (101416284);
  
SELECT ArticleID, ArticleTitle
FROM Articles AS b
WHERE EXISTS
   (
      SELECT ArticleID
      FROM AuthorArticle AS ab
      WHERE b.ArticleID=ab.ArticleID
   )
ORDER BY ArticleTitle;



           
         
    
    
  














Related examples in the same category
1.Subquery: Using EXIST Operator
2.Use the NOT EXISTS operator in the same way as the EXISTS operator, only the opposite results are returned.
3.Sub query with NOT EXISTS command
4.Subquery with EXISTS
5.Exists and subquery
6.Not exists and subquery of constant value
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.