Difference between revisions of "Equality Joins"

From rbachwiki
Jump to navigation Jump to search
(Created page with "'''The most common type of join used in the workplace is based on two (or more) tables having equivalent data stored in a common column. These joins are called equality joins...")
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
'''The most common type of join used in the workplace is based on two (or more) tables having equivalent data stored in a common column. These joins are called equality joins but are also referred to as equijoins, inner joins,
'''The most common type of join used in the workplace is based on two (or more) tables having equivalent data stored in a common column. '''
or simple joins.
'''These joins are called equality joins but are also referred to as equijoins, inner joins, or simple joins.'''
SELECT b.title, b.pubid, p.name
FROM books b, publisher p
WHERE b.pubid = p.pubid
AND (b.cost < 15 OR p.pubid = 1)
ORDER BY title;
 
SELECT c.lastname, c.firstname, b.title
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# = o.customer#
  AND o.order# = oi.order#
  AND oi.isbn = b.isbn
ORDER BY lastname, firstname;
 
SELECT c.lastname, c.firstname, b.title, b.category
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# = o.customer#
  AND o.order# = oi.order#
  AND oi.isbn = b.isbn
  AND category = 'COMPUTER'
ORDER BY lastname, firstname;
 
SELECT b.title, pubid, p.name
FROM publisher p JOIN books b
  USING (pubid)
 
SELECT c.lastname, c.firstname, b.title
FROM customers c JOIN orders o USING (customer#)
    JOIN orderitems oi USING (order#)
    JOIN books b USING (isbn)
WHERE category = 'COMPUTER'
ORDER BY lastname, firstname;
 
 
 
'''There are two main differences between using the USING and ON clauses with the JOIN keyword:'''
*The USING clause can be used only if the tables being joined have a common column with the same name. This rule isn’t a requirement for the ON clause.
* A condition is specified in the ON clause; this isn’t allowed in the USING clause.The USING clause can contain only the name of the common column.
 
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Latest revision as of 23:05, 23 October 2017

The most common type of join used in the workplace is based on two (or more) tables having equivalent data stored in a common column. These joins are called equality joins but are also referred to as equijoins, inner joins, or simple joins.

SELECT b.title, b.pubid, p.name
FROM books b, publisher p
WHERE b.pubid = p.pubid
AND (b.cost < 15 OR p.pubid = 1)
ORDER BY title;
SELECT c.lastname, c.firstname, b.title
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# = o.customer#
 AND o.order# = oi.order#
 AND oi.isbn = b.isbn
ORDER BY lastname, firstname;
SELECT c.lastname, c.firstname, b.title, b.category
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# = o.customer#
 AND o.order# = oi.order#
 AND oi.isbn = b.isbn
 AND category = 'COMPUTER'
ORDER BY lastname, firstname;
SELECT b.title, pubid, p.name
FROM publisher p JOIN books b
 USING (pubid)
SELECT c.lastname, c.firstname, b.title
FROM customers c JOIN orders o USING (customer#)
   JOIN orderitems oi USING (order#)
   JOIN books b USING (isbn)
WHERE category = 'COMPUTER'
ORDER BY lastname, firstname;


There are two main differences between using the USING and ON clauses with the JOIN keyword:

  • The USING clause can be used only if the tables being joined have a common column with the same name. This rule isn’t a requirement for the ON clause.
  • A condition is specified in the ON clause; this isn’t allowed in the USING clause.The USING clause can contain only the name of the common column.

Back To Top- Home - Category