BigRoz Big Roz
Home / Technology / SQL / SQL query join table with some sql query example

SQL query join table with some sql query example

 

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
    SQL query join tables-Left 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
    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
    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

 

Trigger in SQ server 2008

 

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

 

 

 

Author: Technicgang

“Technicgang Started on 2015” Rahul Samant is the founder & CEO of Technicgang. A blog based on science and technology. Techicgang receives 10K subscribers with in year.

About Technicgang

"Technicgang Started on 2015" Rahul Samant is the founder & CEO of Technicgang. A blog based on science and technology. Techicgang receives 10K subscribers with in year.

Check Also

RDBMS

Relational database management system examples

Here I will go to explain relational database management system examples. It includes RDMS concept, …

Leave a Reply

8 Comments on "SQL query join table with some sql query example"

Notify of
Sort by:   newest | oldest | most voted

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 favor of
blogging.

wpDiscuz