Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EXISTS vs JOIN and use of EXISTS clause

Below is the code sample:

CREATE TABLE #titles(     title_id       varchar(20),     title          varchar(80)       NOT NULL,     type           char(12)          NOT NULL,     pub_id         char(4)               NULL,     price          money                 NULL,     advance        money                 NULL,     royalty        int                   NULL,     ytd_sales      int                   NULL,     notes          varchar(200)          NULL,     pubdate        datetime          NOT NULL  )  GO   insert #titles values ('1', 'Secrets',   'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')  insert #titles values ('2', 'The',       'business',     '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')  insert #titles values ('3', 'Emotional', 'psychology',   '0736', $7.99,  $4000.00, 10, 3336,'Note 3','06/12/91')  insert #titles values ('4', 'Prolonged', 'psychology',   '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')  insert #titles values ('5', 'With',      'business',     '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')  insert #titles values ('6', 'Valley',    'mod_cook',     '0877', $19.99, $0.00,    12, 2032,'Note 6','06/09/91')  insert #titles values ('7', 'Any?',      'trad_cook',    '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')  insert #titles values ('8', 'Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')  GO   CREATE TABLE #sales(     stor_id        char(4)           NOT NULL,     ord_num        varchar(20)       NOT NULL,     ord_date       datetime          NOT NULL,     qty            smallint          NOT NULL,     payterms       varchar(12)       NOT NULL,     title_id       varchar(80) )  GO insert #sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1') insert #sales values('2', 'D4482',    '09/14/94', 10, 'Net 60',    '1') insert #sales values('3', 'N914008',  '09/14/94', 20, 'Net 30',    '2') insert #sales values('4', 'N914014',  '09/14/94', 25, 'Net 30',    '3') insert #sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3') insert #sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')   SELECT    title, price FROM      #titles WHERE     EXISTS (SELECT   * FROM      #sales WHERE     #sales.title_id = #titles.title_id AND       qty >30)   SELECT    t.title, t.price FROM     #titles t inner join #sales s on t.title_id = s.title_id where s.qty >30  

I want to know what is the difference between the above 2 queries which gives the same result.Also want to know the purpose of EXISTS keyword and where exactly to use?

like image 920
satyajit Avatar asked Aug 16 '11 17:08

satyajit


People also ask

How do you use exists with join in SQL Server?

JOIN. The EXISTS operator returns TRUE or FALSE while the JOIN clause returns rows from another table. You use the EXISTS operator to test if a subquery returns any row and short circuits as soon as it does. On the other hand, you use JOIN to extend the result set by combining it with the columns from related tables.

Can we use exists with join in SQL?

An EXISTS join is a join in which the right side of the join needs to be probed only once for each outer row. Using such a definition, an EXISTS join does not literally use the EXISTS keyword.


1 Answers

EXISTS is used to return a boolean value, JOIN returns a whole other table

EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.

In your example, the queries are semantically equivalent.

In general, use EXISTS when:

  • You don't need to return data from the related table
  • You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)
  • You want to check existence (use instead of LEFT OUTER JOIN...NULL condition)

If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

If your JOIN key is not indexed, it may be quicker to use EXISTS but you will need to test for your specific circumstance.

JOIN syntax is easier to read and clearer normally as well.

like image 158
JNK Avatar answered Oct 09 '22 11:10

JNK