What is the difference between “INNER JOIN” and “O

Question

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Answer 1

Consider below 2 tables:

EMP

empid   name    dept_id salary
1       Rob     1       100
2       Mark    1       300
3       John    2       100
4       Mary    2       300
5       Bill    3       700
6       Jose    6       400

Department

deptid  name
1       IT
2       Accounts
3       Security
4       HR
5       R&D

Inner Join:

Mostly written as just JOIN in sql queries. It returns only the matching records between the tables.

Find out all employees and their department names:

Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security

As you see above, Jose is not printed from EMP in the output as it's dept_id 6 does not find a match in the Department table. Similarly, HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table.

So, INNER JOIN or just JOIN, returns only matching rows.

LEFT JOIN :

This returns all records from the LEFT table and only matching records from the RIGHT table.

Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
6       Jose    

So, if you observe the above output, all records from the LEFT table(Emp) are printed with just matching records from RIGHT table.

HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table on dept_id.

So, LEFT JOIN returns ALL rows from Left table and only matching rows from RIGHT table.

Can also check DEMO here.

Answer 2

Joins are used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. Lets consider Employee and Location table:

Inner Join:- Inner join creates a new result table by combining column values of two tables (Employee and Location) based upon the join-predicate. The query compares each row of Employee with each row of Location to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row. Here’s what the SQL for an inner join will look like:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Now, here is what the result of running that SQL would look like:

Outer Join:- An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained (left or right).

Left Outer Join:- The result of a left outer join (or simply left join) for tables Employee and Location always contains all records of the "left" table (Employee), even if the join-condition does not find any matching record in the "right" table (Location). Here is what the SQL for a left outer join would look like, using the tables above:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Now, here is what the result of running this SQL would look like:

Right Outer Join:- A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (Location) will appear in the joined table at least once. If no matching row from the "left" table (Employee) exists, NULL will appear in columns from Employee for those records that have no match in Location. This is what the SQL looks like:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Using the tables above, we can show what the result set of a right outer join would look like:

Full Outer Joins:- Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.

Image Source

MySQL 8.0 Reference Manual - Join Syntax

Oracle Join operations

Answer 3

You use INNER JOIN to return all rows from both tables where there is a match. i.e. In the resulting table all the rows and columns will have values.

In OUTER JOIN the resulting table may have empty columns. Outer join may be either LEFT or RIGHT.

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

Answer 4

This is a good diagrammatic explanation for all kind of joins

source: http://ssiddique.info/understanding-sql-joins-in-easy-way.html