SQL Joins Explained

SQL - pronounced as 'see-quill' or ' ess-que-el' - Structured Query Language is a standard programming language used to communicate with a database. 

SQL Joins - .SQL Joins are a set of instructions given to the database to combine data columns from two or more tables.

Database - A database is a structured set of data held in a computer, especially one that is accessible in various ways. In other words database is a collection of information that is organized and stored for it to be accessed, managed and manipulated.

Relational Data - This could be considered to be the backbone of the Join process. A relational data is a piece of data that is linked to other pieces of data likely in a different table. These links are created by "foreign keys" which is nothing more than a piece of data that is unique for another table. 

Types of SQL Joins

Let's look at the different kinds of SQL joins

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Self-Join

Let’s look into the joins, taking them individually..

Inner Join

This is the most famous Join since, if you don't know which join to use your best bet would be this join. An inner join produces a result set that has matching data on both tables. In other words  gets all records from one table that have some related entry in a second table. 

Let's Consider the Following tables to Use for our example:

Sample Table - table1

Sample Table -table2

If we are going to see the Average of each students We will be writing the join statement as follows:- 

SELECT Student_Details.student_id, Student_Details.firstName, Student_Details.lastName , Student_Grades.average

FROM Student_Details

INNER JOIN Student_Grades

ON Student_Details.student_id = Student_Grades.student_id

  

The Result will be 

student_id	firstName	lastName	average
1	Jhon	Stuwart	0.5
2	Vick	Jhonson	1
3	James	George	1.5
4	Raj	Ravi	2

Below is the general syntax/ structure for an inner join 

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

Left Outer Join

A left outer join produces a set of results where all the results from the left hand side are preserved while the results on the right hand side shows the results only if there were matching data for the key field. If there were no matching values it shows 'Null'. In other words,  returns all rows from the left table, even if there are no matches in the right table.

Let's take a the same table from the previous example from Inner Join and add a new student to the table. Where as the student grade doesn't change. Lets say Kamal has not done any exams. The tables are going to look like this

Sample Table - table1

Sample Table - table2

 

Running the following query would join the two tables Student details and grades.

SELECT Student_Details.student_id, Student_Details.firstName, Student_Details.lastName , Student_Grades.average

FROM Student_Details

LEFT JOIN Student_Grades

ON Student_Details.student_id = Student_Grades.student_id

This time the result set is going to be something like the image below..

student_id	firstName	lastName	average
1	Jhon	Stuwart	0.5
2	Vick	Jhonson	1
3	James	George	1.5
4	Raj	Ravi	2
5	Kamal	Raj	NULL

Below is the general syntax/ structure for a left outer join

SELECT table1.column1, table2.column2...

FROM table1

LEFT JOIN table2

ON table1.common_field = table2.common_field