Types of SQL Server Joins

Continuting with the previous article. Let’s discuss the last and final step to understand every type of join.

Step-4: Last step, performing different types of joins.

Let’s check out every join one by one.

Inner Join/Join: Starting with “inner join” or simply “join” keyword which give only the matched rows specified in the ON condition. Our result set would look like this:

--Using join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P join tblFlight F

on P.FlightId = F.FlightId

 

Join

--Using Inner Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P inner join tblFlight F

on P.FlightId = F.FlightId

 

Inner Join

Left Join/ Left Outer Join: Next, “left outer join” or simply “left join” keyword which give all rows from LEFT table and only matched rows from other table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Left Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P left outer join tblFlight F

on P.FlightId = F.FlightId

 

Left Outer Join

--Using Left Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P left join tblFlight F

on P.FlightId = F.FlightId

 

Left Join

Right Outer Join/ Right Join: Next, “right outer join” or simply “right join” which give all rows from RIGHT table and only matched rows from other table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Right Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P right outer join tblFlight F

on P.FlightId = F.FlightId

 

Right Outer Join

--Using Right Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P right join tblFlight F

on P.FlightId = F.FlightId

 

Right Join

Full Outer Join: Next, “full outer join” gives all records from LEFT and RIGHT table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Full Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P full outer join tblFlight F

on P.FlightId = F.FlightId

 

Full Outer Join

Cross Join: At last, “cross join” gives the cross product of two table. Here, it is not reqiuired to specify the ON condition. Our result set would look like this:

--Using Cross Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P cross join tblFlight F

 

Cross Join

For any query, comment us below.

Previous – Types of SQL Server Joins #1

Next – How to create flat files from SQL Server table

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS 

Keep learning and sharing...

Read More »

Simple program

Simple program #14 (Source code to check input is a positive or negative or neutral number)

Enter a number: 64

Number is positive.

The program is written in C# programming language and will accept a number as input. The logic of the program is to check whether it is positive or negative or neutral (neither positive nor negative).

 

Simple program

Note: If you are new to C# and Console Application. Try to code First C# Program

Note: Read articles on how to use Loops and Conditions.

Let’s find out a simple and easy way to code the program.

Practical Implementation:

using System;

namespace patternProblem.Simple_program    //checking input is a number (Positive or negative or neutral)

{

    class Simple14

    {

        public static void Main()

        {

            try

            {

                Console.Write("Enter a number: ");

                double num = Convert.ToDouble(Console.ReadLine());

                if (num > 0)

                {

                    Console.WriteLine("Number is positive.");

                }

                else if (num < 0)

                {

                    Console.WriteLine("Number is negative.");

                }

                else

                {

                    Console.WriteLine("Number 0 is neutral.");

                }

            }

            catch (FormatException)

            {

                Console.WriteLine("Input is not a numbers. It might be an alphabet or special character.");

            }

            catch (OverflowException)

            {

                Console.WriteLine("Input number is exceeding its maximum range.");

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

        }

    }

}

Output:

The input number is 64. The program will check whether input number is positive/ negative/ neutral number. The output is shown below:

Enter a number: 64

Number is positive.

Press any key to continue . . .

For any query, comment us below.

Skip to Main Table Simple program

Previous - Simple program #13

Next – Introduction to Asp.net Web Services

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS 

Keep learning and sharing...

Read More »

Simple program

Simple program #13 (Source code to calculate number of digits)

Enter a number: 456

Number of digits used in given number 456 is 3

The program is written in C# programming language and will accept a number as input. The logic of the program is to counter number of digits.

 

Simple program

Note: If you are new to C# and Console Application. Try to code First C# Program

Note: Read articles on how to use Loops and Conditions.

Let’s find out a simple and easy way to code the program.

Practical Implementation:

using System;

namespace patternProblem.Simple_program

{

    class Simple13   //Calculating number of digits

    {

        static void Main(string[] args)

        {

            Console.Write("Enter a number: ");

            int n = Convert.ToInt32(Console.ReadLine());

            int count = 0;

            Console.WriteLine();

            //Method 1: Using string in-built function length

            Console.WriteLine("Output from Method1: Using string in-built function length");

            Console.WriteLine("Number of digits used in given number {0} is {1}", n, n.ToString().Length);

            Console.WriteLine();

            //Method 2: Using for loop

            int temp = n;

            for (; n > 0; )

            {

                n = n / 10;

                count++;

            }

            Console.WriteLine("Output from Method2: Using for loop");

            Console.WriteLine("Number of digits used in given number {0} is {1}", temp, count.ToString());

            Console.WriteLine();

            //Method 3: Using while loop

            count = 0;

            n = temp;

            while (n > 0)

            {

                n = n / 10;

                count++;

            }

            Console.WriteLine("Output from Method3: Using while loop");

            Console.WriteLine("Number of digits used in given number {0} is {1}", temp, count.ToString());

            Console.ReadKey();

        }

    }

}

Output:

The input number is 4256. The program will count number of digits used in input number. The output is shown below:

Enter a number: 4256

Output from Method1: Using string in-built function length

Number of digits used in given number 4256 is 4

Output from Method2: Using for loop

Number of digits used in given number 4256 is 4

Output from Method3: Using while loop

Number of digits used in given number 4256 is 4

Press any key to continue . . .

For any query, comment us below.

Skip to Main Table Simple program

Previous - Simple program #12

Next – Simple program #14

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS 

Keep learning and sharing...

Read More »