Pros and Cons of the JOIN Clause


The “EXISTS (subquery)” predicate tests whether the set of rows retreived in a subquery is non-empty.

When I'm selecting data from multiple tables I used to use JOINS a lot and recently I started to use another way but I'm unsure of the impact in the long run. Examples:

SELECT * FROM table_1 LEFT JOIN table_2 ON (table_1.column = table_2.column)
So this is your basic LEFT JOIN across tables but take a look at the query below.
SELECT * FROM table_1,table_2 WHERE table_1.column = table_2.column
Personally if I was joining across lets say 7 tables of data I would prefer to do this over JOINS.

Both queries are JOINs, and both use ANSI syntax but one is older than the other. Joins using with the JOIN keyword means that ANSI-92 syntax is being used. ANSI-89 syntax is when you have tables comma separated in the FROM clause, and the criteria that joins them is found in the WHERE clause. When comparing INNER JOINs, there is no performance difference - this: SELECT * FROM table_1 t1, table_2 t2 WHERE t1.column = t2.column ...will produce the same query plan as: SELECT * FROM TABLE_1 t1 JOIN TABLE_2 t2 ON t2.column = t1.column

The main reason to use ANSI-92 syntax is because ANSI-89 doesn't have any OUTER JOIN (LEFT, RIGHT, FULL) support. ANSI-92 syntax was specifically introduced to address this shortcoming, because vendors were implementing their own, custom syntax. Oracle used (+); SQL Server used an asterisk on the side of the equals in the join criteria (IE: t1.column =* t2.column). The next reason to use ANSI-92 syntax is that it's more explicit, more readable, while separating what is being used for joining tables vs actual filteration.

The EXISTS predicate is TRUE if and only if the subquery returns a non-empty set.
[ NOT ] EXISTS (subquery)

Note: Oracle does NOT support Boolean data type; i.e., the Boolean values, TRUE and FALSE, are not supported, but an expression such as EXISTS can return a Boolean value.

Question XV (EXISTS Predicate)
Retrieve all customer names where the customer places an order through agent a05.  

NOT EXISTS can be used to implement the MINUS operator. If R and S are two compatible table (with Head(R) = Head(S) = A1 ... An), then the SQL for difference R–S:

SELECT A1 ... An FROM R WHERE NOT EXISTS ( SELECT * FROM S
  WHERE S.A1 = R.A1 AND ... AND S.An = R.An );

Question XVI (NOT EXISTS Predicate)
Find cid values of customers who do not place any order through agent a03.    
order3
orderno month cid aid pid qty dollar
1011 jan c001 a01 p01 1000 450.00
1012 jan c001 a01 p01 1000 450.00
1019 feb c001 a02 p02 400 180.00
1017 feb c001 a06 p03 600 540.00
1018 feb c001 a03 p04 600 540.00
1023 mar c001 a04 p05 500 450.00
1022 mar c001 a05 p06 400 720.00
1025 apr c001 a05 p07 800 720.00
1013 jan c002 a03 p03 1000 880.00
1026 may c002 a05 p03 800 704.00
1015 jan c003 a03 p05 1200 1104.00
1014 jan c003 a03 p05 1200 1104.00
1021 feb c004 a06 p01 1000 460.00
1016 jan c006 a01 p01 1000 500.00
1020 feb c006 a03 p07 600 600.00
1024 mar c006 a06 p01 800 400.00
customer
cid cname city discnt
c001 TipTop Duluth 10.00
c002 Basics Dallas 12.00
c003 Allied Dallas 8.00
c004 ACME Duluth 8.00
c006 ACME Kyoto 0.00






cid
c004

(Question 16)












cname
TipTop
Basics

(Question 15)












Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

SQL Statement:

Edit the SQL statement and click     to see the result, or  

Result:
The Database includes:
The Database includes:

TablenameRecord
Customers91
Categories8
Employees10
OrderDetails518
Orders196
Products77
Shippers3
Suppliers29




      Do not trust Jack around your expensive glassware —    
      he is all thumbs (clumsy).