MYSQL SQL SERVER

What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN

MYSQL Articles, Errors and Fixes
MYSQL Articles, Errors and Fixes

There are different types of joins available in SQL:

  1. INNER JOIN
  2. OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
  1. INNER JOIN: (the typical Join operation, which uses some comparison operator like = or ).
    • These include equi-joins and natural joins.
    • Inner Joins use a comparison operator to match rows from two tables based on the values in common columns from each table.
  2. OUTER JOIN:
    • Outer joins can be a Left, a Right, or Full Outer Join.
    • Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause
  1. LEFT JOIN or LEFT OUTER JOIN:
    • The result set of a Left Outer Join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match.
    • When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
  2. RIGHT JOIN or RIGHT OUTER JOIN:
    • Right Outer Join is the reverse of a Left Outer Join.
    • All rows from the right table are returned.
    • Null values are returned for the left table any time a right table row has no matching row in the left table.
  3. FULL JOIN or FULL OUTER JOIN:
    • A Full Outer Join returns all rows in both the Left and Right tables.
    • Any time a row has no match in the other table, the select list columns from the other table contain null values.
    • When there is a match between the tables, the entire result set row contains data values from the base tables.
  • Here is a graphic view of all joins that gives clear visual descriptions.
READ  [ Solved -8 Answers ] JAVA - Java function for arrays like PHP's join()?

 

Here is the example for INNER join, LEFT join, RIGHT join and FULL Join.

We have two tables with the following values.

Table A

ID First Name Last Name
1 Wiki techy
2 preethi antony
3 sruthy abc
6 kavitha abc

Table B

ID 2 Age Place
1 24 Kerala
2 24 Usa
3 25 ekm
5 24 Chennai

INNER JOIN

Note :It gives the intersection of the two tables, i.e. rows they have common in TableA and TableB
Syntax:

joins in sql code
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

joins in sql code
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;

Result:

First Name Last Name Age Place
Wiki techy 24 Kerala
preethi antony 24 Usa
sruthy abc 25 ekm

LEFT JOIN

Note : It gives all selected rows in TableA, plus any common selected rows in TableB.
Syntax:

joins in sql code
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

sql code
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;

Result:

First Name Last Name Age Place
Wiki techy 24 Kerala
preethi antony 24 Usa
sruthy abc 25 ekm
kavitha abc Null Null

FULL JOIN

Note : It is same as union operation, it will return all selected values from both tables.
Syntax:

sql server join code
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

sql server join code
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;

Result:

First Name Last Name Age Place
Wiki techy 24 Kerala
preethi antony 24 Usa
sruthy abc 25 ekm
kavitha abc Null Null
Null Null 24 Chennai
  • This article provides some of the basic informations on joins in sql , sql join , join sql , sql join example , sql cross join , sql server join , sql join query , sql join 3 tables , sql update statement , database join , join sql server , outer join sql server , sql query join , joins in dbms , join sql query , joins in database , self join sql server , sql queries for joins , sql join queries examples , dbms joins , joins in rdbms , joins with examples , joins in dbms with examples , sql join syntax , joins in sql server , inner join in dbms , sql join types , join query in sql , types of joins in sql , sql joins tutorial , joins in sql with examples , left outer join in sql server , cross join in sql server , what is join in sql , joining tables in sql , natural join in sql , how to join tables in sql , inner join in sql server , join types , outer join in sql server , different types of joins in sql , w3schools sql join , how to use join in sql , full join in sql , join statement in sql , different joins in sql , left join in sql server , joins in sql server with examples , join command in sql , types of joins in sql server.
READ  [ Solved 100% Working] SNMP service security tab is missing - Windows Server 2012 R2 - DC

About the author

Wikitechy Editor

Wikitechy Editor

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment

X