[Oracle-Sql] C.10 Joining Data from Multiple Tables

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

Purpose of Joins

• Joins are used to link tables and reconstruct data in a relational database

• Joins can be created through:

  • Conditions in a WHERE clause

  • Use of JOIN keywords in FROM clause

Cartesian Joins

• Created by omitting joining condition in the WHERE clause or through CROSS JOIN keywords in the FROM clause
• Results in every possible row combination (m * n)

Cartesian Join Example - Omitted Condition

-- PARTIAL OUTPUT SHOWN
SELECT TITLE, NAME
  FROM BOOKS, PUBLISHER;

Cartesian Join Example - CROSS JOIN keywords

-- PARTIAL OUTPUT SHOWN
SELECT ISBN, TITLE, LOCATION, '     ', COUNT
  FROM BOOKS, WAREHOUSES
  ORDER BY LOCATION, TITLE;

Equality Joins

• Link rows through equivalent data that exists in both tables

• Created by:

  • Creating equivalency condition in the WHERE clause

  • Using NATURAL JOIN, JOIN…USING, or JOIN…ON keywords in the FROM clause

Equality Joins - WHERE Clause Example

SELECT TITLE, NAME
  FROM BOOKS, PUBLISHER
  WHERE BOOKS.PUBID = PUBLISHER.PUBID;

Qualifying Column Names

Columns in both tables must be qualified

SELECT TITLE, PUBID, NAME
  FROM BOOKS, PUBLISHER
  WHERE BOOKS.PUBID = PUBLISHER.PUBID;
--> column ambiguously defined error occurred!

WHERE Clause Supports Join and Other Conditions

SELECT TITLE, BOOKS.PUBID, NAME
  FROM BOOKS, PUBLISHER
  WHERE BOOKS.PUBID = PUBLISHER.PUBID
    AND PUBLISHER.PUBID = 4;

Joining More Than Two Tables

Joining four tables requires three join conditions

Equality Joins - NATURAL JOIN

SELECT TITLE, P.PUBID, NAME
  FROM PUBLISHER NATURAL JOIN BOOKS;

No Qualifiers with a NATURAL JOIN

SELECT TITLE, P.PUBID, NAME
  FROM PUBLISHER NATURAL JOIN BOOKS;
--> "P", "PUBID": invalid identifier error occurred!

Equality Joins - JOIN …USING

SELECT B.TITLE, PUBID, P.NAME
  FROM PUBLISHER P JOIN BOOKS B
    USING (PUBID);

Equality Joins - JOIN …ON

SELECT B.TITLE, B.PUBID, P.NAME
  FROM PUBLISHER P JOIN BOOKS B
    ON P.ID = B.PUBID;

JOIN Keyword Overview

• Use JOIN…USING when tables have one or more columns in common
• Use JOIN…ON when same named columns are not involved or a condition is needed to specify a relationship other than equivalency (next section)
• Using the JOIN keyword frees the WHERE clause for exclusive use in restricting rows

Non-Equality Joins

• In WHERE clause, use any comparison operator other than the equal sign
• In FROM clause, use JOIN…ON keywords with a non-equivalent condition

Equality Joins - WHERE Clause Example

SELECT B.TITLE, P.GIFT
  FROM BOOKS B, PROMOTION P
  WHERE B.RETAIL BETWEEN P.MINRETAIL AND P.MAXRETAIL;

Non-Equality Joins - JOIN …ON Example

SELECT B.TITLE, P.GIFT
  FROM BOOKS B JOIN PROMOTION P
    ON B.RETAIL BETWEEN P.MINRETAIL AND P.MAXRETAIL;

Self-Joins

• Used to link a table to itself
• Requires the use of table aliases
• Requires the use of a column qualifier

Customer Table Example

Self Joins - WHERE Clause Example

SELECT R.FIRSTNAME, R.LASTNAME, C.LASTNAME "REFERRED"
  FROM CUSTOMERS C, CUSTOMERS R
  WHERE C.REFERRED = R.CUSTOMER#;

Self Joins - JOIN …ON Example

SELECT R.FIRSTNAME, R.LASTNAME, C.LASTNAME "REFERRED"
  FROM CUSTOMERS C JOIN CUSTOMERS R
    ON C.REFERRED = R.CUSTOMER#;

Outer Joins

• Use outer joins to include rows that do not have a match in the other table
• In WHERE clause, include outer join operator (+) immediately after the column name of the table with missing rows to add NULL rows
• In FROM clause, use FULL, LEFT, or RIGHT with OUTER JOIN keywords

Outer Joins - WHERE Clause Example

SELECT C.LASTNAME, C.FIRSTNAME, O.ORDER#
  FROM CUSTOMERS C, ORDERS O
  WHERE C.CUSTOMER# = O.CUSTOMER#
  ORDER BY C.LASTNAME, C.FIRSTNAME;

Outer Joins - OUTER JOIN Keyword Example

SELECT C.LASTNAME, C.FIRSTNAME, O.ORDER#
  FROM CUSTOMERS C, ORDERS O
  WHERE C.CUSTOMER# = O.CUSTOMER#(+)
  ORDER BY C.LASTNAME, C.FIRSTNAME;

If multiple join conditions are used, the outer join condition may be required in all of the join conditions to retain non-matching rows

In previous versions of Oracle an error would be raised if the outer join operator is used on the same table in more than one join operation

Set Operators

Used to combine the results of two or more SELECT statements

Set Operator	Description
UNION	Returns the results of both queries and removes duplicates
UNION ALL	Returns the results of both queries but includes duplicates
INTERSECT	Returns only the rows included in the results of both queries
MINUS	Subtracts the second query’s results if they’re also returned in the first query’s results

Set Operators - UNION Example

SELECT BA.AUTHORID
  FROM BOOKS B JOIN BOOKAUTHOR BA
    USING (ISBN)
  WHERE CATEGORY = 'FAMILY LIFE'
UNION
SELECT BA.AUTHORID
  FROM BOOKS B JOIN BOOKAUTHOR BA
    USING (ISBN)
  WHERE CATEGORY = 'CHILDREN';

Set Operators - INTERSECT Example

SELECT CUSTOMER#
  FROM CUSTOMERS
INTERSECT
SELECT CUSTOMER#
  FROM ORDERS;

Set Operators - MINUS Example

SELECT CUSTOMER#
  FROM CUSTOMERS
MINUS
SELECT CUSTOMER#
  FROM ORDERS;

Summary

• Data stored in multiple tables regarding a single entity can be linked together through the use of joins
• A Cartesian join between two tables returns every possible combination of rows from the tables; the resulting number of rows is always m * n
• An equality join is created when the data joining the records from two different tables are an exact match
• A non-equality join establishes a relationship based upon anything other than an equal condition
• Self-joins are used when a table must be joined to itself to retrieve needed data
• Inner joins are categorized as being equality, non-equality, or self-joins
• An outer join is created when records need to be included in the results without having corresponding records in the join tables
• The record is matched with a NULL record so it will be included in the output
• Set operators such as UNION, UNION ALL, INTERSECT, and MINUS can be used to combine the results of multiple queries

C.10 Demo

-- Week 10

--CARTESIAN JOIN --20 CUSTOMER, 14 BOOKS 20*14=280
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM BOOKS;

-- TABLE 1
SELECT COUNT(*)FROM AUTHOR; --> 14 RECORDS
-- TABLE 2
SELECT COUNT(*)FROM PUBLISHER; --> 5 RECORDS

-- CARTESIAN JOIN RESULTS
SELECT LNAME, FNAME, NAME, CONTACT FROM AUTHOR, PUBLISHER; --> NEED TO SHOW 70 RECORDS (14x5 = 70)

SELECT ISBN,TITLE,FIRSTNAME,LASTNAME,CITY FROM BOOKS,CUSTOMERS;

-- JOIN
--SELECT FIELDNAME FROM TBLE1 JOIN TBLE2 USING (COMMONFIELD)
SELECT TITLE,ISBN,PUBID,NAME FROM BOOKS JOIN PUBLISHER USING(PUBID)
ORDER BY NAME,TITLE;
-- JOIN CUSTOMERS AND ORDERS TABLE
select Lastname,Orderdate from CUSTOMERS join ORDERS using (Customer#);

-- EQUALITY JOINS
SELECT * FROM BOOKS;
SELECT ISBN,COST,CONTACT,PHONE FROM BOOKS, PUBLISHER
WHERE BOOKS.PUBID=PUBLISHER.PUBID
AND COST>30 AND CONTACT='JANE TOMLIN'
ORDER BY COST; --> 14 RECORDS

-- CONNECT ORDERS AND ORDERITEMS TABLE, CHECK WHICH ORDERS ARE NOT SHIPPED TO CUSTOMERS
SELECT * FROM ORDERS;
SELECT * FROM ORDERITEMS;

SELECT ISBN,ITEM#,SHIPDATE,SHIPCOST FROM ORDERS,ORDERITEMS
WHERE ORDERS.ORDER#=ORDERITEMS.ORDER#
AND SHIPDATE IS NULL;

-- CREATE "COLUMN AMBIGUOUSLY DEFINED"
SELECT ISBN FROM BOOKS,BOOKAUTHOR;
SELECT PUBID FROM BOOKS,PUBLISHER;

-- CONNECT BOOKS AND PUBLISHER TABLE, LIST CHILDREN BOOKS PUBLISHED BY PUBLISHER
SELECT * FROM BOOKS;
SELECT * FROM PUBLISHER;

SELECT TITLE, BOOKS.PUBID, NAME
FROM BOOKS, PUBLISHER
WHERE BOOKS.PUBID=PUBLISHER.PUBID
AND BOOKS.CATEGORY='CHILDREN';

SELECT TITLE, BOOKS.PUBID, NAME
FROM BOOKS, PUBLISHER
WHERE BOOKS.PUBID=PUBLISHER.PUBID
AND CATEGORY IN ('CHILDREN');

SELECT TITLE, BOOKS.PUBID, NAME
FROM BOOKS, PUBLISHER
WHERE BOOKS.PUBID=PUBLISHER.PUBID
AND CATEGORY IN (SELECT CATEGORY FROM BOOKS WHERE CATEGORY='CHILDREN');

SELECT TITLE, BOOKS.PUBID, NAME
FROM BOOKS, PUBLISHER
WHERE BOOKS.PUBID=PUBLISHER.PUBID
AND CATEGORY IN ('CHILDREN');

SELECT TILE, CATEGORY FROM BOOKS WHERE CATEGORY='CHILDREN';

SELECT CATEGORY FROM BOOKS WHERE CATEGORY='CHILDREN' AND PUBID = 2;

-- CUSTOMER AND ORDERS TABLE CONNECT
SELECT LASTNAME,FIRSTNAME,ORDER#,SHIPDATE
FROM CUSTOMERS,ORDERS
WHERE CUSTOMERS.CUSTOMER#=ORDERS.CUSTOMER#
AND SHIPDATE IS NULL;

SELECT LASTNAME,FIRSTNAME,ORDER#,SHIPDATE
FROM CUSTOMERS C,ORDERS O
WHERE C.CUSTOMER#=O.CUSTOMER#
AND SHIPDATE IS NULL;

-- EQUALITY JOINS - NATURAL JOIN -> ERROR
SELECT TITLE,P.PUBID,NAME
FROM PUBLISHER P NATURAL JOIN BOOKS B;
-- FIX ERROR
SELECT B.TITLE,PUBID,P.NAME
FROM PUBLISHER P JOIN BOOKS B
USING(PUBID);

SELECT B.TITLE,P.NAME
FROM PUBLISHER P JOIN BOOKS B
ON P.PUBID=B.PUBID;

-- CONNNECT BOOKAUTHOR AND AUTHOR USING CLAUSE
SELECT * FROM BOOKAUTHOR;
SELECT * FROM AUTHOR;

SELECT AUTHORID,A.LNAME,A.FNAME,BA.ISBN
FROM BOOKAUTHOR BA JOIN AUTHOR A
USING(AUTHORID) ORDER BY FNAME;

--ON CLAUSE
SELECT TITLE,ISBN,NAME FROM BOOKS JOIN PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID
ORDER BY NAME;

-- ERROR - ambiguously defined
SELECT TITLE,ISBN,PUBID,NAME FROM BOOKS JOIN PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID
ORDER BY NAME;
--CORRECTED QUERY
SELECT TITLE,ISBN,BOOKS.PUBID,NAME FROM BOOKS JOIN PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID
ORDER BY NAME;
SELECT TITLE,ISBN,PUBLISHER.PUBID,NAME FROM BOOKS JOIN PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID
ORDER BY NAME;

-- FILTER WITH CATEGORY AND PUBLISHER NAME
SELECT TITLE,CATEGORY,ISBN,BOOKS.PUBID,NAME FROM BOOKS JOIN PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID
 WHERE CATEGORY='COMPUTER' AND NAME='PUBLISH OUR WAY' ORDER BY NAME;

--COLUMN ALIAS
SELECT B.TITLE,B.ISBN,P.NAME FROM BOOKS B JOIN PUBLISHER P USING(PUBID);
SELECT B.TITLE,B.ISBN,P.NAME FROM BOOKS B JOIN PUBLISHER P ON  B.PUBID=P.PUBID;
SELECT BOOKS.TITLE,BOOKS.ISBN,PUBLISHER.NAME FROM BOOKS JOIN
PUBLISHER ON BOOKS.PUBID=PUBLISHER.PUBID;

-- INNERMOST JOIN
select * FROM  CUSTOMERS join ORDERS using (Customer#);
SELECT * FROM CUSTOMERS JOIN ORDERS ON
CUSTOMERS.CUSTOMER#=ORDERS.CUSTOMER#;
SELECT * FROM CUSTOMERS C  JOIN ORDERS O ON
C.CUSTOMER#=O.CUSTOMER#;

SELECT COUNT(*) FROM ORDERS;

-- LIST OF CUSTOMERS WHO HAVE ORDERS
 SELECT C.LASTNAME,C.FIRSTNAME,O.ORDER# FROM CUSTOMERS C,ORDERS O
 WHERE C.CUSTOMER#=O.CUSTOMER# ORDER BY C.FIRSTNAME;

--OUTERJOIN . LIST OF CUSTOMERS WHO DID NOT ORDER ANY ITEMS
SELECT C.LASTNAME,C.FIRSTNAME,O.ORDER# FROM CUSTOMERS C,ORDERS O
WHERE C.CUSTOMER#=O.CUSTOMER#(+) AND ORDER# IS NULL
ORDER BY C.FIRSTNAME;

-- LIST OF CUSTOMERS WHO ORDERED
SELECT C.LASTNAME,C.FIRSTNAME,O.ORDER# FROM CUSTOMERS C,ORDERS O
WHERE C.CUSTOMER#=O.CUSTOMER#(+) AND ORDER# IS NOT  NULL
ORDER BY C.FIRSTNAME;

-- SELF JOIN

SELECT * FROM CUSTOMERS;
SELECT * FROM CUSTOMERS WHERE REFERRED IS NOT NULL;
SELECT C.CUSTOMER#,C.LASTNAME,C.FIRSTNAME ,R.CUSTOMER# AS REFERRED ,R.LASTNAME AS "REFEREED PERSON" FROM
CUSTOMERS C JOIN CUSTOMERS R ON C.REFERRED=R.CUSTOMER#;
----------------------------------------------------------------------------

-- CHALLEGE 1
-- REWRITE THE  BELOW QUERY BY USING - USING CLAUSE ,ON CLAUSE
SELECT B.TITLE,P.CONTACT FROM PUBLISHER P,BOOKS B WHERE P.PUBID=B.PUBID;

select TITLE,CONTACT from PUBLISHER join BOOKS using (PubID);

select TITLE,CONTACT from PUBLISHER join BOOKS on PUBLISHER.PubID = BOOKS.PubID ;

SELECT B.TITLE,P.CONTACT FROM PUBLISHER P JOIN BOOKS B USING(PUBID);

select  B.TITLE,P.CONTACT from PUBLISHER P join BOOKS B on P.PubID = B.PubID ;

----------------------------------------------------------------------------

-- CHALLEGE 2 - 3 QUERY
-- DISPLAY FIRSTNAME,LASTNAME,ORDER# . USE CUSTOMERS & ORDERS TABLE
SELECT FIRSTNAME,LASTNAME,ORDER# FROM CUSTOMERS JOIN ORDERS USING(CUSTOMER#);

SELECT Firstname,Lastname,Order# from CUSTOMERS join ORDERS on CUSTOMERS.Customer# = ORDERS.Customer#;

SELECT FIRSTNAME,LASTNAME,ORDER# FROM ORDERS,CUSTOMERS
WHERE CUSTOMERS.CUSTOMER#=ORDERS.CUSTOMER#;

SELECT C.FIRSTNAME,C.LASTNAME,O.ORDER# FROM ORDERS O,CUSTOMERS C
WHERE C.CUSTOMER#=O.CUSTOMER#;

----------------------------------------------------------------------------

SELECT COUNT(*) FROM BOOKS;

SELECT COUNT(*) FROM PUBLISHER;

SELECT *  FROM PUBLISHER;

SELECT TITLE,AUTHORID FROM BOOKS,BOOKAUTHOR;

SELECT TITLE,NAME ,BOOKS.PUBID,COST,CONTACT FROM BOOKS,PUBLISHER WHERE
BOOKS.PUBID=PUBLISHER.PUBID
AND BOOKS.PUBID=1 AND COST >15;

-- EXAMPLE OF ABIGUOUS ERROR. TO FIX ADD THE TABLE NAME WITH THE COMMON FIELD
SELECT ISBN,TITLE,QUANTITY FROM BOOKS,
ORDERITEMS WHERE
BOOKS.ISBN=ORDERITEMS.ISBN;

-- CONNECT CUSTOMERS AND ORDERS
SELECT CUSTOMERS.CUSTOMER#,LASTNAME,ADDRESS ,ORDER#,SHIPDATE FROM CUSTOMERS ,ORDERS
WHERE CUSTOMERS.CUSTOMER#=ORDERS.CUSTOMER#
AND SHIPDATE IS NULL;
-- CONNECT BOOKAUTHOR & AUTHOR TABLE . USE AND ,OR ,IN OPERATOR

SELECT * FROM BOOKAUTHOR;
SELECT ISBN,AUTHOR.AUTHORID,LNAME FROM
BOOKAUTHOR,AUTHOR WHERE
AUTHOR.AUTHORID=BOOKAUTHOR.AUTHORID
AND FNAME='JAMES';

--IN OPERATOR OR
SELECT ISBN,AUTHOR.AUTHORID,LNAME FROM
BOOKAUTHOR,AUTHOR WHERE
AUTHOR.AUTHORID=BOOKAUTHOR.AUTHORID
AND AUTHOR.AUTHORID IN ('S100','J100','P100') ORDER BY AUTHORID;

-- SUBQUERY
SELECT AUTHOR.AUTHORID FROM AUTHOR WHERE AUTHORID IN ('S100','J100','P100') ;

SELECT ISBN,AUTHOR.AUTHORID,LNAME FROM
BOOKAUTHOR,AUTHOR WHERE
AUTHOR.AUTHORID=BOOKAUTHOR.AUTHORID
AND AUTHOR.AUTHORID IN (SELECT AUTHOR.AUTHORID FROM AUTHOR WHERE AUTHORID IN ('S100','J100','P100') );

-- CHALLENGE 1
-- JOIN CUSTOMERS,ORDERS,ORDERITEM ,BOOKS . WHICH CUSTOMER
--HAS ORDERED WHAT BOOK.
--O/P   BONITAS ORDERED THE BOOK BODYBUILD IN 1O
SELECT c.firstname || ' ' || c.lastname AS name, b.title
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn);

SELECT CONCAT(CONCAT(FIRSTNAME,' ORDERED '),TITLE) AS "CUSTOMER ORDER"  FROM CUSTOMERS
 JOIN ORDERS USING(CUSTOMER#)
 JOIN ORDERITEMS USING (ORDER#)
 JOIN BOOKS USING(ISBN);


SELECT CONCAT(CONCAT(CONCAT(CONCAT(c.lastname,'-'),C.FIRSTNAME),' ORDER THE BOOK '), b.title)
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn);

SELECT * FROM ORDERS;
SELECT * FROM ORDERITEMS;

-- CHALLEGE 2 . HOW MANY DAYS ARE BETWEEN ORDERDATE AND SHIPDATE

SELECT CONCAT(CONCAT(CONCAT(CONCAT(c.lastname,'-'),C.FIRSTNAME),' ORDER THE BOOK '), b.title),
(o.shipdate-o.orderdate) as days,o.SHIPDATE
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn)
where o.shipdate is not null;


SELECT order#,CONCAT(shipdate - orderdate,'DAY(S)') "DTIME" FROM
ORDERS ;

SELECT CONCAT(CONCAT(CONCAT(SHIPDATE, ' SHIPPED '), ORDERDATE), SHIPDATE - ORDERDATE)  FROM ORDERS
INNER JOIN orderitems USING (ORDER#);


SELECT CONCAT(CONCAT(CONCAT(CONCAT('ORDER #', ORDER#), ' TOOK '),(SHIPDATE-ORDERDATE)), ' DAYS TO SHIP.')
AS DTIME
FROM ORDERS
WHERE SHIPDATE IS NOT NULL;


SELECT customer#,firstname || '''s order ' || DECODE(shipdate - orderdate, 0, 'shipped immediately!', 'took ' || (shipdate - orderdate) || ' day(s) to ship.') "Order info"
FROM customers
INNER JOIN orders USING (customer#)
WHERE shipdate IS NOT NULL
ORDER BY (shipdate - orderdate);

---

Back to Top

See other articles in Category Oracle-Sql