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

ORDERNUMBERCUSTOMERIDYEAR
132010
252015
3852011

 

Then, have a look “Customers” table consist of CUSTOMERID, FIRSTNAME, PINCODE

CUSTOMERIDFIRSTNAMEPINCODE
1RAHUL400001
2ROHIT400071
3SUDIP415616

 

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

ORDERNUMBERCUSTOMERIDYEARCUSTOMERIDFIRSTNAMEPINCODE
1320103SUDIP415616

 

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

OperatorUseExample
=Equal toA.Column1=B.column1
<less thanA.column1 < 500
>Greater thanA.column1 > 100
<=Less than equal toA.column1 <= 500
=>Greater than equal toA.column1 => 100
!=Not equal toA.column1 != ‘SUDIP’
betweenBetweena.column1 between value1 and value2
LikeLikeA.column1 like %S
NOTNOTA.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

STATECODESTATENAME
1JAMMU & KASHMIR
2HIMACHAL PRADESH
3PUNJAB
4CHANDIGARH
5UTTARANCHAL
6HARYANA
7DELHI

 

ALLINDIADISTRICT table consist of

DISTRICTCODESTATECODEDISTRICTNAME
11Jammu
22Kangra
32Kullu
42Hamirpur
527ABC

 

 

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

STATECODESTATENAMEDISTRICTCODESTATECODEDISTRICTNAME
1JAMMU & KASHMIR11Jammu
2HIMACHAL PRADESH22Kangra
2HIMACHAL PRADESH32Kullu
2HIMACHAL PRADESH42Hamirpur
3PUNJAB
4CHANDIGARH
5UTTARANCHAL
6HARYANA
7DELHI

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

 

 

STATECODESTATENAMEDISTRICTCODESTATECODEDISTRICTNAME
1JAMMU & KASHMIR11Jammu
2HIMACHAL PRADESH22Kangra
2HIMACHAL PRADESH32Kullu
2HIMACHAL PRADESH42Hamirpur
 527ABC

 

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

 

STATECODESTATENAMEDISTRICTCODESTATECODEDISTRICTNAME
1JAMMU & KASHMIR11Jammu
2HIMACHAL PRADESH22Kangra
2HIMACHAL PRADESH32Kullu
2HIMACHAL PRADESH42Hamirpur
3PUNJAB
4CHANDIGARH
5UTTARANCHAL
6HARYANA
7DELHI
 527ABC

 

 

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

STATECODESTATENAMEDISTRICTCODESTATECODEDISTRICTNAME
1JAMMU & KASHMIR11Jammu

 

 

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

STATECODESTATENAMEDISTRICTCODESTATECODEDISTRICTNAME
1JAMMU & KASHMIR11Jammu
2HIMACHAL PRADESH22Kangra
2HIMACHAL PRADESH32Kullu
2HIMACHAL PRADESH42Hamirpur

 

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

 

 

 

🏅BAML Cards|✅Best Bank Of America Merrill Lynch Visa Reward Card Review 2020 {GUIDE}

Are you doubtful about the Bank Of America Merrill Lynch Card? Do you need a complete guide on the reward card? The...

Quick Tips About Intel Iris Graphics 6100 | Updated

Are you looking for the information related to Intel Iris Graphics 6100, or you just looking for Intel information to keep yourself...

Download Free Spotify Premium Apk | Spotify Mod Apk Free

Are you looking for a free spotify premium apk? If the answer the question is a yes, then you're at the right place. We've...

How To Download Xender For PC Windows 7,8,10 and Mac

Are you looking for information related to Xender application, or you looking for how to download Xender for PC, or you just...

✅Best Credit One Bank Login|🔒Secure Registration Guide|2020

Are you in search of a reliable Credit One Bank login and registration guide? If yes, then this is the place to...

🏅Everbank Login|✅Best Guidelines|For Amazing 2020

Are you looking for information about EverBank, or you just simply looking for the guidelines to log into the Everbank financial...

✅Best Guide for www.acgcardservices.com|myoffer| Enter Confirmation Code for AAA Dollars MasterCard|For Amazing 2020|[💯Verified]

Are you looking for the credit card guidelines, or you just simply want to know about credit card for the informative purpose,...

💻How To Download Jio TV for PC and Laptop | Windows 7,8,10 | Jio TV on Windows Without SimCard✅

We all must be aware of that Reliance Jio is a fast-growing industry. It has bought a digital revolution in India. Jio stands for Joint...

🥇15 Best ExtraTorrent Alternatives|💯Extratorrent Top Proxy Sites 2020|

Is ExtraTorrent blocked in your country? Have you been looking for ExtraTorrent Alternatives? Have you been looking out for ExtraTorrent Proxy Sites?...

🎖American Express Card|✅Best Reliable Card Guide|2020

American Express charge and credit cards include various advantages in the kind of rewards points and travel bonuses. American Express...

Related Stories

8 Comments

  1. 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. . . . . .

  2. 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.

  3. Thanks in support of sharing such a good thought,
    post is pleasant, thats why i have read it completely

  4. 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.

  5. 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!

  6. This piece of writing is actually a pleasant one it helps new net users, who are wishing in favour of
    blogging.

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox