Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL Equi Join


SQL Equi join is a specific type comparison base join (equally comparison) not allowing other comparison operator such as <, > <= etc. And create record set result that are combining columns value from the tables (two or more table).

SQL Equi Join

Example Table

Considering following category, product is our example table.

SQL> SELECT * FROM category;
CATEGORY_IDCATEGORY_NAME
1Mobiles
2Laptops
3Laptops
4Cameras
5Gaming



SQL> SELECT * FROM product;
CATEGORY_IDPRODUCT_NAME
1Nokia
1Samsung
2HP
2Dell
3Apple
4Nikon
NullPlaystation

Run it...   »


SQL Equi join use JOIN keyword specify table name and ON keyword specify the join predicate condition.

Example Statement :

SQL> SELECT *
    FROM product JOIN category
    ON product.category_id = category.category_id;

CATEGORY_ID PRODUCT_NAME          CATEGORY_ID CATEGORY_NAME
----------- --------------------- ----------- ---------------------
          1 Nokia                           1 Mobiles
          1 Samsung                         1 Mobiles
          2 HP                              2 Laptops
          2 Dell                            2 Laptops
          3 Apple                           3 Tablet
          4 Nikon                           4 Cameras

6 rows selected.

Run it...   »


You can also write Equi join as following way.

Example Statement :

SQL> SELECT *
	FROM product, category
	WHERE product.category_id = category.category_id;

CATEGORY_ID PRODUCT_NAME          CATEGORY_ID CATEGORY_NAME
----------- --------------------- ----------- ---------------------
          1 Nokia                           1 Mobiles
          1 Samsung                         1 Mobiles
          2 HP                              2 Laptops
          2 Dell                            2 Laptops
          3 Apple                           3 Tablet
          4 Nikon                           4 Cameras

6 rows selected.

Run it...   »


If join predicate condition both table column name are same, then you can write this query shorthand way by using USING Keyword.

Example Statement :

SQL> SELECT *
	FROM product 
	INNER JOIN category USING (category_id);

CATEGORY_ID PRODUCT_NAME          CATEGORY_ID CATEGORY_NAME
----------- --------------------- ----------- ---------------------
          1 Nokia                           1 Mobiles
          1 Samsung                         1 Mobiles
          2 HP                              2 Laptops
          2 Dell                            2 Laptops
          3 Apple                           3 Tablet
          4 Nikon                           4 Cameras

6 rows selected.

Run it...   »