SQL Inner Join Examples

Updated on 09 Jan 2012,
Published on 28 Feb 2008

Different types of SQL Server Inner Join Examples

Try the example of joining the single table to itself. Self join in sql server 2000/2005.

Equi Join (Inner Join) for combining the columns of two sql database tables into single table output result. Try sql inner equi join examples.

Natural Join (Inner Join) for getting the output of equi join into the specified columns format and removing the ambiguous column names from the output. See sql inner natural join examples.

SQL Inner Join Query with (=) Operator

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

This inner join query will return the categoryid, categoryname, productid, productname, unitprice where product unit price = 10

SQL Inner Join Query with (>) Operator

SELECT		DISTINCT C.CATEGORYID, 
			C.CATEGORYNAME
FROM		CATEGORIES C 
INNER JOIN	PRODUCTS P 
ON			C.CATEGORYID > P.CATEGORYID
WHERE		P.UNITPRICE = 10
ORDER BY	C.CATEGORYNAME

This inner join query will return the categoryId, categoryName having products with unit price=10

SQL Inner Join Query with not equal (<>) Operator

SELECT		DISTINCT P1.PRODUCTNAME, 
			P1.UNITPRICE, 
			P1.SUPPLIERID
FROM		PRODUCTS P1 
INNER JOIN	PRODUCTS P2
ON			P1.SUPPLIERID = P2.SUPPLIERID
AND			P1.UNITPRICE <> P2.UNITPRICE
WHERE		P1.UNITPRICE < 20 AND P2.UNITPRICE < 20
ORDER BY	P1.SUPPLIERID

Inner Join with not equal operator is rarely used in self joins. As an example above sql self join query returns the productname, unitprice, supplierid where suppliers having 2 or more than 2 products with unit price less than 20.

Continue to next tutorial: SQL Left Outer Join Examples to learn the implementation of left outer join of sql server tables using sql query.

0 Responses to "SQL Inner Join Examples"
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email