SQL INNER JOIN

Inner join refers to the matching values in both the tables.

If we want to fetch Department_name and name of all employees.

e- Shorthand name for Employee

d-Shorthand name for Department

Query(without using join):

Every record in one table(Employee) joins with every record in another table(Department).

Query:

SELECT e.name, d.D_name

FROM Employee e, Department d;

“N “ records in Employee table will refer to “N” records in Department table and we will get “NxN records in our output table.

Here in the above table :

AA — — —-à HR, this is correct as AA belongs to Dep_id 111 and referring to another table i.e. Department with Dep_id 111 belongs to HR D_name.

AA — — —-àFinance, this is incorrect as AA belongs to HR its dept_id is 111 not 113 in the Department table.

AA — — — —à Production, this is also incorrect as the dept_id for production in the Department table is 114.

The above output table has not referred the Dep_id, in both the tables, as we have not mentioned in our Query.

If we will refer to two of the tables we can see a similar attribute between them i.e. Dep_id

To filter out the incorrect records, we need to create a join between them using the “WHERE “ clause.

Query(using join):

SELECT e.name, d.D_name

FROM Employee e, Department d

WHERE e.Dep_id = d.Dep_id;

So, the name BB will be ignored and the output table will be the same.

And here we are with our correct output table.

As we have considered two tables, we were using one join between two tables.

We can conclude: When there is N number of tables, then there will be always N-1 number of Joins.

One Problem for you:

Now can you try for 3 tables, without using join and with using join (Fetch City_name for all employees)? Take a look while solving on the number of joins.

[Hint: join between employee table and department table i.e. with Dep_id, another join between Department and Location i.e. using Loc_id]