Oracle INNER JOIN

What is Inner Join in Oracle?

The INNER join is such a join when equijoins and nonequijoins are performed, rows from the source and target tables are matched using a join condition formulated with equality and inequality operators, respectively. These are referred to as inner joins.

Syntax:

SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Pictorial presentation of Syntax:

Pictorial presentation of Inner Join:

Example:

The following SQL statement will return department no, department name and the city for a the same location no.

Sample table: locations

Sample table: departments

SELECT a.department_id, a.department_name, b.city
FROM departments a
INNER JOIN locations b
ON a.location_id = b.location_id;

Sample Output:

DEPARTMENT_ID DEPARTMENT_NAME                CITY
------------- ------------------------------ ---------------------
           60 IT                             Southlake
           50 Shipping                       South San Francisco
           10 Administration                 Seattle
           30 Purchasing                     Seattle
           90 Executive                      Seattle
          100 Finance                        Seattle
          110 Accounting                     Seattle
          120 Treasury                       Seattle
          130 Corporate Tax                  Seattle
          140 Control And Credit             Seattle
          150 Shareholder Services           Seattle
          160 Benefits                       Seattle
          170 Manufacturing                  Seattle
          180 Construction                   Seattle
          190 Contracting                    Seattle
          200 Operations                     Seattle
          210 IT Support                     Seattle
          220 NOC                            Seattle
          230 IT Helpdesk                    Seattle
          240 Government Sales               Seattle
          250 Retail Sales                   Seattle
          260 Recruiting                     Seattle
          270 Payroll                        Seattle
           20 Marketing                      Toronto
           40 Human Resources                London
           80 Sales                          Oxford
           70 Public Relations               Munich

27 rows selected.

INNER JOINS: SQL and other Relational Databases

• SQL INNER JOIN
• MySQL INNER JOIN
• SQLite INNER JOIN
• PostgreSQL INNER JOIN

Previous: JOINS with USING Clause
Next: LEFT OUTER JOIN