SQL Inner Natural Join Examples

Updated on 03 Mar 2012,
Published on 28 Feb 2008

In the previous article you learnt how to use equi inner join in sql queries to join two tables and retrieve the combined result of both sql database tables. See the example of equi join here… SQL Inner Equi Join Examples. Notice that equi join sql query returned the categoryId column twice because of relation between two tables. Products table also has categoryId column that shows the product belongs to a particular category in categories whose categoryId is also saved in products table as a relational key between both the tables. 

You can specify the required column names using sql natural join query.

Natural Join Query Example

SELECT		C.*, 
			P.PRODUCTID, 
			P.PRODUCTNAME 
FROM		CATEGORIES C 
INNER JOIN	PRODUCTS P 
ON			P.CATEGORYID = C.CATEGORYID

This natural join query will return all the columns of categories table and prodcutId and productName from products table.

You can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.

Inner Join Query Example by Specifying Column Names

SELECT		C.CATEGORYID, 
			C.CATEGORYNAME, 
			P.PRODUCTID, 
			P.PRODUCTNAME, 
			P.UNITPRICE 
FROM		CATEGORIES C 
INNER JOIN	PRODUCTS P 
ON			P.CATEGORYID = C.CATEGORYID

This inner join query will display only the specified column names of both the tables.

Note: In actual SQL Server does not support natural joins. This type of Inner Join is considered as equivalent to the natural join.

Continue to next tutorial: SQL Inner Join Examples to learn the implementation of inner join.

3 Responses to "SQL Inner Natural Join Examples"
firefight
You are confusing INNER JOIN with a NATURAL JOIN--a natural join does not have an ON clause. SQL Server does not support natural joins.
EvilOverlord
Agreed. This is NOT a natural join. Page 180 of the SQL-92 standard states "If NATURAL is specified, then a shall not be specified" (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). All you're doing is selecting all columns of a table - something completely different from a natural join. Natural joins, Equi Joins, Non-Equi Joins, Inner Joins all relate (as you might expect from the name) to the JOIN syntax, not the column list. whether you specify "select *", "select c.*" or "select col1, col2, etc" has absolutely nothing to do with what type of join you are performing. Please read the sql-92 standard, do a few google searches or pick up a book on SQL and then rewrite the above article to use the correct nomenclature.
Ezineasp.net
Hi @EvilOverlord Yes you are right. SQL Server does not support natural join but the join type we described above is the equivalent one.
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email