INNER JOIN

INNER JOIN returns all rows whose joining field/column values exist in the tables. It may be easy to visualize it from a Venn diagram perspective where Tables A and B are the two circles. The inner join is represented by the intersection of the two tables. An example of this is seen below.

INNER JOIN in ACTION

To demonstrate INNER JOIN, we will use a previous set of tables

employees table

select employee_id, email,  department_id, phone from employees limit 10;

employee_id |              email              | department_id |  phone
------------+---------------------------------+---------------+----------
          1 | [email protected]        |             1 | 555-0101
          2 | [email protected]     |             2 | 555-0102
          3 | [email protected]     |             3 | 555-0103
          4 | [email protected]       |             4 |
          5 | [email protected]      |             1 | 555-0105
          6 | [email protected]     |             6 | 555-0106
          7 | [email protected]     |             3 | 555-0107
          8 | [email protected] |             2 | 555-0108
          9 | [email protected]      |             1 |
         10 | [email protected]  |             5 | 555-0110
(10 rows)

departments table

select * from departments;

department_id | department_name  |  budget   |  location  | manager_employee_id
--------------+------------------+-----------+------------+---------------------
            1 | Sales            | 500000.00 | New York   |
            2 | Marketing        | 350000.00 | California |
            3 | IT               | 750000.00 | Texas      |
            4 | HR               | 200000.00 | New York   |
            5 | Finance          | 300000.00 | Illinois   |
            6 | Customer Service | 180000.00 | Florida    |
            7 | Operations       | 400000.00 |            |
(7 rows)

Now we implement the INNER JOIN, returning all instances that exist at the intersection of tables A and B.

SELECT
    a.employee_id,
    a.email,
    a.phone,
    b.department_name,
    b.budget, b.location
FROM employees a
INNER JOIN departments b
USING (department_id)
ORDER BY employee_id
LIMIT 10;

 employee_id |              email              |  phone   | department_name  |  budget   |  location
-------------+---------------------------------+----------+------------------+-----------+------------
           1 | [email protected]        | 555-0101 | Sales            | 500000.00 | New York
           2 | [email protected]     | 555-0102 | Marketing        | 350000.00 | California
           3 | [email protected]     | 555-0103 | IT               | 750000.00 | Texas 
           4 | [email protected]       |          | HR               | 200000.00 | New York
           5 | [email protected]      | 555-0105 | Sales            | 500000.00 | New York
           6 | [email protected]     | 555-0106 | Customer Service | 180000.00 | Florida
           7 | [email protected]     | 555-0107 | IT               | 750000.00 | Texas
           8 | [email protected] | 555-0108 | Marketing        | 350000.00 | California
           9 | [email protected]      |          | Sales            | 500000.00 | New York
          10 | [email protected]  | 555-0110 | Finance          | 300000.00 | Illinois
(10 rows)

USING and WHERE Clause VS. ON

You will notice that in the INNER JOIN, I am using USING to specify the column. It is not restricted to INNER JOIN but often you may find with INNER JOIN that people use it.