>> INNER or EQUI JOIN in SQL

INNER or EQUI  JOIN :

This join is the most common join. This join returns the records which satisfy the given join condition from both sides ,ie, the result satisfies the given join condition from all the tables involved(there might be two or more than two tables involved in SQL join.) .

Syntax:

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1, TABLE2, ...

WHERE TABLE1_COLUMN = TABLE2_COLUMN

The above syntax can also be written in a different way as below :

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 JOIN TABLE2

ON {join condition}

OR

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 INNER JOIN TABLE2

ON {join condition}

Let's take an existing table EMP_INFO as below :

NAME	DEPARTMENT	SALARY	POSITION	STATE	HIREDATE
DAVID	MARKETING	2000	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	HR	TX	5-Jan-06
RAM	RESOURCES	1200	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	MARKETER	FL	9-Feb-08
BRITNI	TECHNICAL	1800	ASSISTANT	PA	1-Feb-09
ADAM	TECHNICAL	2200	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	MANAGER	VA	21-Aug-12

And let's take another existing table DEPT_INFO as below:

STATE	LOCATION	ESTD
VA	RICHMOND	1990
MD	TOWSON	1988
TX	AUSTIN	1995
IL	ROCKFORD	1980
AR	LITTLE ROCK	2000
CO	DENVER	2005

Now, let's find out the employee name, their Department, the LOCATION, STATE and the respective establishment year.

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD
FROM EMP_INFO A , DEPT_INFO B
WHERE A.STATE = B.STATE ;

OR

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD
FROM EMP_INFO A JOIN  DEPT_INFO B
ON A.STATE = B.STATE ;

OR

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD
FROM EMP_INFO A  INNER  JOIN DEPT_INFO B
ON A.STATE = B.STATE ;

NAME	DEPARTMENT	STATE	LOCATION	ESTD
DAVID	MARKETING	IL	ROCKFORD	1980
RICHARD	SALES	MD	TOWSON	1988
SMITH	RESOURCES	TX	AUSTIN	1995
RAM	RESOURCES	TX	AUSTIN	1995
ADAM	TECHNICAL	MD	TOWSON	1988
MIKE	EXECUTIVE	IL	ROCKFORD	1980
JAMES	MARKETING	VA	RICHMOND	1990
JULI	EXECUTIVE	VA	RICHMOND	1990

Note: A, B in above queries are aliases. Aliases are temporary names given to table. An alias is used in SQL queries only and exists only  until the duration of the SQL execution. If there is more than one tables involved in the queries or if you want to give more relevant/readable name or to shorten the long name or to give an unique name replacing the table name in the query, you can use aliases.

If there are more than one conditions, you can add the additional conditions using AND.

Syntax:

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1, TABLE2, ...

WHERE

TABLE1_COLUMN = TABLE2_COLUMN (this is condition 1 which is join condition)
AND
conditio2
AND
condition3
AND
so on.

OR

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 JOIN (or INNER JOIN) TABLE2

ON {join condition}
AND
condition2
AND
condition3
AND
so on.

For example:

If you want to see the POSITIONS available in each LOCATION in the state of TX, you can write the query as below:

SELECT A.POSITION,A.STATE, B.LOCATION
FROM EMP_INFO A , DEPT_INFO B
WHERE A.STATE = B.STATE
AND
A.STATE = 'TX';

OR

SELECT A.POSITION,A.STATE, B.LOCATION
FROM EMP_INFO A INNER JOIN DEPT_INFO B
ON A.STATE = B.STATE
AND
A.STATE = 'TX';

POSITION	STATE	LOCATION
HR	TX	AUSTIN
ASSISTANT	TX	AUSTIN

NEXT

Go To Main SQL Page


.