PostgreSQL INNER JOIN

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

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

OR

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

OR

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

Example of Inner Join with ON clause

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

Or can be written as :

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

Example of Inner Join with ON and WHERE clause

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

Example of Inner Join with ON for specific columns

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;

Example of Inner Join with USING clause

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

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

SELECT * 
FROM invoice
NATURAL INNER JOIN item;

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

SELECT * 
FROM customer
NATURAL INNER JOIN item;

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.