PostgreSQL INNER JOIN

How to use Inner Join in PostgreSQL?

The Inner Join will determine which rows from both participating tables are considered to return on a match between the columns. The ON or USING clause is used with join condition. The ON clause in join condition takes a boolean expression as taken by WHERE clause and match the common columns of participating tables when the boolean expression evaluates true and determine which rows have to be joined.

On the other hand, the USING clause takes a list of column names separated by a comma, which is common in both the participating table and performed a join on the matching of each of these pairs of columns. The USING clause returns common column once as output and followed by the remaining columns and the common columns will appear first when '*' is used with a SELECT statement.

Syntax:

Syntax:

SELECT [* | column_list]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column.name; 

OR

Syntax:

SELECT [* | column_list]
FROM table1
INNER JOIN table2
USING (column.name);

OR

SELECT [* | column_list]
FROM table1,table2
WHERE table.column_name=table2.column_name;

Sample table: Customer

Sample table: Item

Sample table : Invoice

Example of Inner Join with ON clause

Code:

SELECT * 
FROM invoice
INNER JOIN item
ON invoice.item_no=item.item_no;

Or can be written as:

Code:

SELECT * 
FROM invoice,item
WHERE 
invoice.item_no=item.item_no;

Output:

Pictorial Presentation for the above example

Example of Inner Join with ON and WHERE clause

Code:

SELECT * 
FROM invoice
INNER JOIN item
ON invoice.item_no=item.item_no
WHERE 
item.rate>=10;

Output:

Example of Inner Join with ON for specific columns

Code:

SELECT invoice.invoice_no,invoice.cust_no, invoice.sold_qty,
item.item_no,item.item_descrip
FROM invoice
INNER JOIN item
ON invoice.item_no=item.item_no
WHERE 
item.rate>=10;

Output:

Example of Inner Join with USING clause

Code:

SELECT * 
FROM invoice
INNER JOIN item
USING (item_no);

Output:

Explanation

In the above example, the 'item_no' column appear first and only once, because the USING clause has used.

Example of Inner Join with NATURAL clause

Code:

SELECT * 
FROM invoice
NATURAL INNER JOIN item;

Output:

Explanation

In the above example, the 'item_no' column appear only once, because this column is common in both the tables.

Inner Join with NATURAL clause behaves like CROSS JOIN

Code:

SELECT * 
FROM customer
NATURAL INNER JOIN item;

Output:

Explanation

In the above example, there are no matching rows between the participating tables; so all the columns appear in this join and it behaves like a cross join.

Previous: CROSS JOIN
Next: LEFT JOIN