SQL - Inner Join

Query data from a single table using the SELECT.

Query data from multiple tables to have a complete result set for analysis.

To query data from multiple tables you use join statements.

Types of joins such as inner join, outer join ( left outer join or left join, right outer join or right join, and full outer join) and self join.

1.     One category can have many products.

2.     One product belongs to one and only one category.

Therefore, there is a many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is the categoryid column.

SELECT * FROM SUPPLIERS;

SELECT * FROM CATEGORIES;

SELECT * FROM PRODUCTS;

SQL INNER JOIN – querying data from two tables example

SELECT

    PRODUCTID, PRODUCTNAME, CATEGORYNAME

FROM

    PRODUCTS

INNER JOIN

    CATEGORIES ON CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID;

SQL INNER JOIN – querying data from three tables

SELECT

    PRODUCTID,

    PRODUCTNAME,

    CATEGORYNAME,

    COMPANYNAME AS SUPPLIER

FROM

    PRODUCTS

INNER JOIN

    CATEGORIES ON CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID

INNER JOIN

    SUPPLIERS ON SUPPLIERS.SUPPLIERID = PRODUCTS.SUPPLIERID

Implicit SQL INNER JOIN

SELECT

    PRODUCTID, PRODUCTNAME, CATEGORYNAME

FROM

    PRODUCTS,

    CATEGORIES

WHERE

    PRODUCTS.CATEGORYID = CATEGORIES.CATEGORYID;