SQL query join tables are used to join rows from more than one tables.Here i will go to explain SQL query join tables next post i will go with SQL queries interview questions and answers.
SQL query join table
An SQL query to join table is used to integrate rows from two or more tables, to combine result SQL join query depends on a common field between two tables.
The most frequent used SQL query join table is: SQL INNER JOIN (simple join).
SQL inner join used to recived all records from one or more than tables when particular the join criteria is met.
SQL query examples in SQL server –SQL Inner join
Here we consider orders table with 3 column ORDERNUMBER, CUSTOMERID, YEAR
| ORDERNUMBER | CUSTOMERID | YEAR |
| 1 | 3 | 2010 |
| 2 | 5 | 2015 |
| 3 | 85 | 2011 |
Then, have a look “Customers” table consist of CUSTOMERID, FIRSTNAME, PINCODE
| CUSTOMERID | FIRSTNAME | PINCODE |
| 1 | RAHUL | 400001 |
| 2 | ROHIT | 400071 |
| 3 | SUDIP | 415616 |
Here, two tables customer and orders having common column CUSTOMERID, means CUSTOMERID tuple from orders table link to the “CUSTOMERID” in the CUSTOMER table. CUSTOMERID provide Primar– foreign key relation between order and customer table.
SQL join table query
Select * from orders Inner join Customer On orders.CUSTOMERID=customer.CUSTOMERID
Result
| ORDERNUMBER | CUSTOMERID | YEAR | CUSTOMERID | FIRSTNAME | PINCODE |
| 1 | 3 | 2010 | 3 | SUDIP | 415616 |
Syntax for SQL query join table
Select * from table1 Inner join table2 On table1.Column=table2.Column
Another ways to join
Select * from table1, table2 where table1.column=table2.column
Different types of SQL join query for multiple tables
Before we resume with SQL query examples in SQL server, first we will understand different kind of SQL query to JOIN table.
- LEFT JOIN: To expand result little bit, we recommended LEFT Join. Left join give all records from left table and only corresponding records from right join table. Sometime Left Join is also called as LEFT OUTER Join
Left joins - INNER JOIN: Inner join shows all result when minimum one entry common between two tables.
- RIGHT JOIN: Right join work as exact opposite of left join. Right join gives all the records from right table and only selected records from left table. Sometime right join is also called as right outer join.
Sql query join table -Right join - FULL JOIN: Full join means complete .Full join returns all the records from both tables.
SQL Query join table-Full join
Important operator – join tables
| Operator | Use | Example |
| = | Equal to | A.Column1=B.column1 |
| < | less than | A.column1 < 500 |
| > | Greater than | A.column1 > 100 |
| <= | Less than equal to | A.column1 <= 500 |
| => | Greater than equal to | A.column1 => 100 |
| != | Not equal to | A.column1 != ‘SUDIP’ |
| between | Between | a.column1 between value1 and value2 |
| Like | Like | A.column1 like %S |
| NOT | NOT | A.column1 is not null |
Let’s start with SQL query join example
Consider ALLINDIASTATE table consist of two column STATECODE and STATENAME
| STATECODE | STATENAME |
| 1 | JAMMU & KASHMIR |
| 2 | HIMACHAL PRADESH |
| 3 | PUNJAB |
| 4 | CHANDIGARH |
| 5 | UTTARANCHAL |
| 6 | HARYANA |
| 7 | DELHI |
ALLINDIADISTRICT table consist of
| DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | 1 | Jammu |
| 2 | 2 | Kangra |
| 3 | 2 | Kullu |
| 4 | 2 | Hamirpur |
| 5 | 27 | ABC |
Left join example -:
Syntax : Select * from table1
Left join or left outer join
Table2 on table1.column1=table2.column2
SELECT * FROM ALLINDIASTATE
LEFT JOIN
ALLINDIADISTRICT
ON
ALLINDIASTATE.STATECODE=ALLINDIADISTRICT.STATECODE
RESULT
| STATECODE | STATENAME | DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | JAMMU & KASHMIR | 1 | 1 | Jammu |
| 2 | HIMACHAL PRADESH | 2 | 2 | Kangra |
| 2 | HIMACHAL PRADESH | 3 | 2 | Kullu |
| 2 | HIMACHAL PRADESH | 4 | 2 | Hamirpur |
| 3 | PUNJAB | |||
| 4 | CHANDIGARH | |||
| 5 | UTTARANCHAL | |||
| 6 | HARYANA | |||
| 7 | DELHI |
Right join example -:
Syntax Select * from table1
Right join or Right outer join
Table2 on table1.column1=table2.column2
SELECT * FROM ALLINDIASTATE
RIGHT JOIN
ALLINDIADISTRICT
ON
ALLINDIASTATE.STATECODE=ALLINDIADISTRICT.STATECODE
RESULT
| STATECODE | STATENAME | DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | JAMMU & KASHMIR | 1 | 1 | Jammu |
| 2 | HIMACHAL PRADESH | 2 | 2 | Kangra |
| 2 | HIMACHAL PRADESH | 3 | 2 | Kullu |
| 2 | HIMACHAL PRADESH | 4 | 2 | Hamirpur |
| 5 | 27 | ABC |
Syntax Select * from table1
Full join or full outer join
Table2 on table1.column1=table2.column2
SELECT * FROM ALLINDIASTATE
FULL JOIN
ALLINDIADISTRICT
ON
ALLINDIASTATE.STATECODE=ALLINDIADISTRICT.STATECODE
RESULT
| STATECODE | STATENAME | DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | JAMMU & KASHMIR | 1 | 1 | Jammu |
| 2 | HIMACHAL PRADESH | 2 | 2 | Kangra |
| 2 | HIMACHAL PRADESH | 3 | 2 | Kullu |
| 2 | HIMACHAL PRADESH | 4 | 2 | Hamirpur |
| 3 | PUNJAB | |||
| 4 | CHANDIGARH | |||
| 5 | UTTARANCHAL | |||
| 6 | HARYANA | |||
| 7 | DELHI | |||
| 5 | 27 | ABC |
SQL query join table -SQL Cross Join:
SQL Cross join (X) used to obtain all accessible combination between more than one SQL query join table, for example consider ALLINDIASTATE table consist of 7 tuple and ALLINDIADISTRICT table consist of 5 Records so all available resulting records will be 7 x 5 = 35 records.
The syntax of cross join is a small change compare two other SQL query join tables syntax, In cross join we will not give the primary key – foreign key. Cross join perform direct join without specifying any relation or comparison.
Syntax for
SQL cross join query
Select * from table1 cross join table2
Sql query example
SELECT * FROM ALLINDIASTATE CROSS JOIN ALLINDIADISTRICT
Result total – 7*5=35 RECORDS
SQL Self Join:
Self-join do not have any different syntax, but it’s proficient by giving the same SQL table name using an alias, and therefore we can employ “Inner” or “Outer” SQL joins.
Sql query example
(SELECT * FROM table1) as X Inner JOIN (SELECT * FROM table1) as Y On X.column1=y.column2
The depth summery
How does SQL query Join table actually works?
1- INNER JOINS:
Considering the following SQL query example:
SELECT column1, column2… FROM table1 JOIN table2 ON table1.Column1 = table2 .column2
Algorithm used to calculated record
For each rows as Rt1 in table1
For each rows as Rt2 in table2
If Rt1 joins with Rt2
Return (Rt1, Rt2)
Here SQL server use two nested for loop and condition statement .First for loop hold every rows from table1 and second for loop hold table2 then if condition is examine . If specified condition satisfied then it return all records.
2- OUTER JOINS
Little bit similar with inner join
Select column1, column2. . From table1 Left join table2 on table1 .column1=table2.column2
After executing this SQL outer join query, the following algorithm work as:
For each rows as RT1 in table1
Begin
For each rows as RT2 in the table2
If (RT1 match RT2)
Return (RT1, RT2)
Else
Return (RT1, NULL)
End
For SQL Right Outer join algorithms applied as:
For each rows as RT1 in table1
Begin
For each rows as RT2 in the table2
If (RT1 match RT2)
Return (RT1, RT2)
Else
Return (Null, RT2)
End
SQL left outer join used two nested loop, first for loop it grab all rows from table1 and in second for loop it takes all rows from table2 then if statement check whether given condition is matched or not. If given condition is satisfied then it return all matching records otherwise it return only table1 records. Similarly in right outer join but difference is if condition is not matched then it returns all records from table2.
SQL query join table -Advanced SQL Query Examples
SQL Query join table – Non EQUI join
NON EQUI JOIN
The SQL NON EQUI JOIN apply comparison operator .Some of comparison operator are >, < for details checked Important operator.
Syntax for NON EQUI JOIN
Select * from table1, table2 where table1.column1 |comparison operator|table2.column2
SQL query examples
Select * from ALLINDIASTATE, ALLINDIADISTRICT where ALLINDIASTATE.STATECODE < 2
Result
| STATECODE | STATENAME | DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | JAMMU & KASHMIR | 1 | 1 | Jammu |
SQL EQUI JOIN
This kind of join work on matching condition of the respected tables. SQL EQUI JOIN use (=) for comparison operator.
Syntax
Select * from table1, table2 where table1.column=table2.column
SQL query examples
Select * from ALLINDIASTATE, ALLINDIADISTRICT
where
ALLINDIASTATE.STATECOD=ALLINDIADISTRICT. STATECODE
Result
| STATECODE | STATENAME | DISTRICTCODE | STATECODE | DISTRICTNAME |
| 1 | JAMMU & KASHMIR | 1 | 1 | Jammu |
| 2 | HIMACHAL PRADESH | 2 | 2 | Kangra |
| 2 | HIMACHAL PRADESH | 3 | 2 | Kullu |
| 2 | HIMACHAL PRADESH | 4 | 2 | Hamirpur |
Similar Post : Cascaded dropdown using Stored procedure , Group by clause
Sql query join table explain various kind of joins in SQL.We also Focused on advanced SQl query join table and their algorithm.You can also watch example in youtube
Hey very cool blog!! Man .. Beautiful .. Wonderful .. I’ll bookmark your website and take
the feeds also? I’m happy to seek out so many useful info right here in the put up,
we want work out extra strategies in this regard, thank you for
sharing. . . . . .
Ahaa, its pleasant conversation concerning this article at this place at this blog, I have read all that,
so at this time me also commenting here.
Thanks in support of sharing such a good thought,
post is pleasant, thats why i have read it completely
Good post. I learn something new and challenging on blogs I stumbleupon every day.
It will always be helpful to read through articles from other authors and use a little something from other web
sites.
Amazing blog! Do you have any tips for aspiring writers?
I’m planning to start my own website soon but I’m a little lost on everything.
Would you advise starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely confused ..
Any ideas? Bless you!
Great delivery. Solid arguments. Keep up the great spirit.
This blog was… how do I say it? Relevant!! Finally, I have found something that helped me. Appreciate it!
This piece of writing is actually a pleasant one it helps new net users, who are wishing in favour of
blogging.