Monday, 29 December 2014

JOINS

JOINS


Joins in SQL Server are used to select the data from multiple tables using a single select statement.

T-SQL provides the join concept, which allows retrieval of data from more than one table. This concept is probably the most important for RDBMS, because it allows data to be spread over many tables.

In SQL Server there existed three types of joins which includes

  1. INNER JOIN
  2. OUER JOIN
  3. CROSS JOIN

Sample Tables

EMP

EMPNO       ENAME      SAL                   DEPTNO     
----------- ---------- --------------------- -----------
11          RAGHU      10000.0000            10
22          RAZ            20000.0000            20
33          AMAR        10000.0000            10
44          MANI          15000.0000            20
55          CHARN      15000.0000            40
66          ANIL           20000.0000            50

DEPT

DEPTNO      DNAME     LOC       
----------- ---------- ----------
10          SALES               HYD
20          HR                     CHE
30          IR                       BAN











1.INNER JOIN: Inner join selects the data from multiple tables based on the equality condition It means it selects only matched records from the multiple tables. For doing this Inner join operation we should have to maintain one common valued column in the multiple tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 INNER JOIN TABLE2
       ON TABLE1.COMMON COLUMN=TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME FROM EMP INNER JOIN DEPT
       ON EMP.DEPTNO=DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME     
----------- ---------- ----------- ----------
11          RAGHU                 10          SALES
22          RAZ                       20          HR
33          AMAR                  10          SALES
44          MANI                    20          HR



2. OUTER JOIN: It is the extension of Inner Join operation because Inner selects only matched records from multiple tables where Outer join selects matched records as well as unmatched records. It includes

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

  1. Left Outer Join: It selects matched records from both the tables as well as unmatched records from Left side table. For doing this operation we have to keep a special symbol ‘*’ at the left side of the equality condition.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 LEFT OUTER JOIN TABLE2
       ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP LEFT OUTER JOIN DEPT
       ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME     
----------- ---------- ----------- ----------
11          RAGHU               10          SALES
22          RAZ                    20          HR
33          AMAR                10          SALES
44          MANI                 20          HR
55          CHARN         NULL        NULL
66          ANIL              NULL        NULL


  1. Right Outer Join: It selects matched records from both the tables as well as unmatched records from Right side table. For doing this operation we have to keep a special symbol ‘*’ at the right side of the equality condition.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 RIGHT OUTER JOIN  TABLE2
    ON TABLE1.COMMON COLUMN  = TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP RIGHT OUTER JOIN  DEPT
       ON EMP.DEPTNO = DEPT.DEPTNO



EMPNO       ENAME      DEPTNO      DNAME     
----------- ---------- ----------- ----------
11          RAGHU               10          SALES
22          RAZ                    20          HR
33          AMAR                10          SALES
44          MANI                 20          HR
NULL    NULL                 30          IR


  1. Full Outer Join: It is just combination of Left outer Join + Right outer join. It selects matched records as well as unmatched records from the given tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 FULL OUTER JOIN TABLE2
       ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP FULL OUTER JOIN DEPT
       ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME     
----------- ---------- ----------- ----------
11          RAGHU               10          SALES
22          RAZ                    20          HR
33          AMAR                10          SALES
44          MANI                 20          HR
55          CHARN         NULL        NULL
66          ANIL              NULL        NULL
NULL   NULL                 30           IR


CROSS-JOIN:
 It is also known as CROSS PRODUCT or CARTESIAN PRODUCT because it produces the product of multiple tables. Every row from first table is multiplied with all rows of another table. Simply it is the multiplication of two tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,…….
                TABLE2.COLUMN1,TABLE2.COLUMN2,…….
FROM TABLE1 CROSS JOIN TABLE2

Ex:

SELECT EMP.EMPNO, EMP.ENAME,
                DEPT.DEPTNO, DEPT.DNAME
FROM EMP CROSS JOIN DEPT




EMPNO       ENAME      DEPTNO      DNAME     
----------- ---------- ----------- ----------
11          RAGHU                 10          SALES
22          RAZ                       10          SALES
33          AMAR                   10          SALES
44          MANI                    10          SALES
55          CHARN                 10          SALES
66          ANIL                      10          SALES
11          RAGHU                 20          HR
22          RAZ                       20          HR
33          AMAR                   20          HR
44          MANI                    20          HR
55          CHARN                 20          HR
66          ANIL                     20          HR
11          RAGHU                30          IR
22          RAZ                      30          IR
33          AMAR                  30          IR
44          MANI                   30          IR
55          CHARN                30          IR
66          ANIL                    30          IR


SELF JOIN:

Join a table with itself by providing two table alias names is called SELF-JOIN.

Select * from DEPT, DEPT

The above statement shows an error message because it not possible to multiply a table by itself with the same name, so that we have to project the same table DEPT as two tables to the SQL Server. To show a single DEPT table as two tables to server we have to use the concept of table Alias Names.

SELECT * FROM DEPT A, DEPT B

In the above example we provided two table alias names for the single table DEPT those are A and B. Then server identifies that there are two tables available and it performs join operation in a normal way. According user point of view there existed only one table but according to Server point of view there are two tables available those are A and B.






DEPTNO      DNAME      LOC        DEPTNO      DNAME      LOC       
----------- ---------- ---------- ----------- ---------- ---------- ----------------
10                  SALES        HYD             10          SALES      HYD
20                    HR            CHE               10          SALES      HYD
30                     IR             BAN              10          SALES      HYD
10                  SALES         HYD             20          HR         CHE
20                     HR            CHE               20          HR         CHE
30                      IR             BAN               20          HR         CHE
10                    SALES       HYD              30          IR         BAN
20                    HR             CHE                30          IR         BAN
30                      IR              BAN               30          IR         BAN


























� L t �� � .5in'>DELETE FROM EMP WHERE DEPTNO=10


E.g.: Write a Query to delete all employ who is working under 20th department and employ number 33

DELETE FROM EMP WHERE DEPTNO=20 AND EMPNO=33

eftM � ; e �� � justify;text-indent: 0in;mso-list:none;tab-stops:.5in'> 




Step3: Add your required constraints to columns

Syntax:
ALTER TABLE TABLENAME ADD CONSTRAINTTYPE (COLUMN1),
                                                                CONSTRAINTTYPE (COLUMN2),………..

E.g.:
ALTER TABLE DEPT ADD PRIMARY KEY (DEPTNO),
                                                 UNIQUE (DNAME),
                                                DEFAULT ‘HYD’ FOR LOC

E.g.:
ALTER TABLE EMP ADD PRIMARY KEY (EMPNO),
                                                 CHECK (SAL>=1000),
                                     FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)

Adding constraints for the existing table with constraint names:

Step1 and Step2 are common here also

Step3: Add your required constraints to columns

Syntax:
ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN1), CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN2),………………..

 E.g.:
ALTER TABLE DEPT ADD CONSTRAINT PK PRIMARY KEY (DEPTNO),
                                                 CONSTRAINT UQ UNIQUE (DNAME),
                                                CONSTRAINT DF DEFAULT ‘HYD’ FOR LOC

E.g.:
ALTER TABLE EMP ADD CONSTRAINT PRK PRIMARY KEY (EMPNO),
                                              CONSTRAINT CK CHECK (SAL>=1000),
        CONSTRAINT FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)


Dropping The Constraints:

Syntax:
ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAME

E.g.: ALTER TABLE DEPT DROP CONSTRAINT PK

E.g.: ALTER TABLE EMP DROP CONSTRAINT PRK

Note:  If you create any table with out any constraint names then server will arrange the constraint names in its own format. Those constraint names will be displayed when you execute a stored procedure SP_HELPCONSTRAINT Table_Name.

SP_HELPCONSTRAINT:
 This Stored Procedure is used to display the description of constraints which have been placed on different columns of a specific table.

Syntax:
SP_HELPCONSTRAINT Table-Name

Ex:
SP_HELPCONSTRAINT EMP

o-bidj �%n - �� � pt;font-family:Batang; mso-fareast-font-family:"Times New Roman";mso-hansi-font-family:Batang; mso-bidi-font-family:Helvetica'>s = split(str," ")

For i = ubound(s) to 0 step -1
revstring = revstring&" "&s(i)
Next
msgbox revstring

29 Print the data as a Pascal triangle
30 Join elements of an array as a string

31 Trim a given string from both sides
32 Write a program to insert 100values and to delete 50 values from an array

dim a(100)

For i = 1 to 100
a(i) = i

If a(i) = 51 Then
            Exit for
            Erase a(i)
End If
print a(i)
Next

33 Write a program to force the declaration of variables
34 Write a program to raise an error and print the error number. 
35 Finding whether a variable is an Array

Isarray(a) = true ‘if the variable is really an array’
Isarray(a) = false ‘if the variable is not an array’

36 Write a program to Convert value into a currency
37 Write a program to Convert an expression to a date
38 Display current date and Time
39 Find difference between two dates.
40 Add time interval to a date
41 Print current day of the week
42 Convert Date from Indian Format to US format
43 Find whether current month is a long month
44 Find whether given year is a leap year
45 Format Number to specified decimal places
46 Write a program to Generate a Random Number
47 Write a program to show difference between Fix and Int 
48 Write a program to find subtype of a variable
49 Write a program to print the decimal part of a given number
50 Write a Function to return a random number
51 Write a Function to add and multiply two numbers
52 Write a Sub Procedure to print “Hello World”
53 Write a Sub Procedure to add and multiply two numbers
54 Write a program to list the Timezone offset from GMT
55 what is the difference between ByVal and ByVal



Function getval(byval x, byref y)
   x = x+1
   y = y+1
print "x="&x&"           "&"y="&y
End Function

Dim a: a = 5
dim b:  b = 5
call getval( a, b )
print "a="&a&"           "&"b="&b


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)