SQL Examples – How to use JOINS

SELECT * FROM INVOICE CROSS JOIN LINE;
SELECT INVOICE.INV_NUMBER, CUS_CODE, INV_DATE, P_CODE
FROM INVOICE CROSS JOIN LINE;
SELECT INVOICE.INV_NUMBER, CUS_CODE, INV_DATE, P_CODE
FROM INVOICE, LINE;
: --- NATURAL JOIN 
: DOES NOT REQUIRE TABLE QUALIFIER
SELECT CUS_CODE, CUS_LNAME, INV_NUMBER, INV_DATE
FROM CUSTOMER NATURAL JOIN INVOICE;
SELECT INV_NUMBER, P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE NATURAL JOIN LINE NATURAL JOIN PRODUCT;
: --- JOIN USING 
SELECT INV_NUMBER, P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE JOIN LINE USING (INV_NUMBER) 
JOIN PRODUCT USING (P_CODE);
: --- JOIN ON 
SELECT INVOICE.INV_NUMBER, P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE;
SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME
FROM EMP E JOIN EMP M ON E.EMP_MGR = M.EMP_NUM
ORDER BY E.EMP_MGR;
: --- OUTER JOINS
: --- LEFT OUTER JOIN
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;
: --- RIGHT OUTER JOIN
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR RIGHT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;
: --- FULL OUTER JOIN
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR FULL JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

Leave a Reply

Your email address will not be published. Required fields are marked *