Equality 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. 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