Introducing San Francisco History of the Tower of London Grand Central Station Visitors Guide

Tuesday 13 December 2011

SELECT INTO

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table.        

Syntax :

 we can select only the columns we want into the new table:

SELECT Column1, Column2, Column3,
INTO Table2
FROM Table1


We can select all columns into the new table:

SELECT *
INTO Table2
FROM Table1

The "Pur" Table


OrderNo
custname
city
1
Siva
Srivilliputtur
2
Bala
Sivakasi
3
Kanna
Madurai



Example :


select * into CopyPUr from Pur where custname='Bala'





SQL FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntax 


SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name



Pur

OrderNo
custname
city
1
Siva
Srivilliputtur
2
Bala
Sivakasi
3
Kanna
Madurai

PurDet

sno
OrderNo
qty
rate
1
2
50
70
2
2
30
56
3
1
100
200
4
3
30
30
5
1
60
80



Example :



SELECT Pur.OrderNo as PurOrdNo,Pur.custname, Pur.City,PurDet.Orderno as PurDetOrdNo
FROM Pur
Full Join PurDet
ON Pur.Orderno=PurDet.OrderNo 
ORDER BY Pur.CustName


Output :

PurOrdNo
custname
city
PurDetOrdNo
NULL
NULL
NULL
5
NULL
NULL
NULL
4
2
Bala
Sivakasi
2
2
Bala
Sivakasi
2
3
Kannan
Madurai
NULL
1
Siva
Srivilliputtur
1



Quick Links :


      Refer Left  & Right Join ....  Click Me 
  

Monday 5 December 2011

SQL LEFT JOIN & RIGHT JOIN




The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables


                                                                    Left Join




The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are
 no matches in the right table (table_name2).

Syntax :

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The Pur table

OrderNo
custname
city
1
Siva
Srivilliputtur
2
Bala
Sivakasi
3
Kanna
Madurai



The PurDet table

sno
OrderNo
qty
rate
1
2
50
70
2
2
30
56
3
1
100
200
4
3
30
30
5
1
60
80



Example 



SELECT Pur.OrderNo as PurOrdNo,Pur.custname, Pur.City,PurDet.Orderno as PurDetOrdNo
FROM Pur
LEFT JOIN PurDet
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName

PurOrdNo
custname
city
PurDetOrdNo
2
Bala
Sivakasi
2
2
Bala
Sivakasi
2
3
Kanna
Madurai
NULL
1
Siva
Srivilliputtur
1


                                           Right Join


The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are
 no matches in the left table (table_name1).

Syntax 


SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name



Example



SELECT Pur.OrderNo as PurOrdNo,Pur.custname, Pur.City,PurDet.Orderno as PurDetOrdNo
FROM Pur
RIGHT JOIN Pur
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName



PurOrdNo
custname
city
PurDetOrdNo
NULL
NULL
NULL
5
NULL
NULL
NULL
4
2
Bala
Sivakasi
2
2
Bala
Sivakasi
2
1
Siva
Srivilliputtur
1

SQL INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name



The "Pur" table


OrderNo
Custname
City
1
Siva
Srivilliputtur
2
Bala
Sivakasi
3
Kanna
Madurai



The "Purdet" table



Sno
OrderNo
Qty
Rate
1
2
50
70
2
2
30
56
3
1
100
200
4
5
30
30
5
4
60
80





Example :



SELECT PurDet.OrderNo,Pur.custname, Pur.City
FROM Pur
INNER JOIN PurDet
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName





OrderNo
custname
city
2
Bala
Sivakasi
2
Bala
Sivakasi
1
Siva
Srivilliputtur







SQL JOINS


  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables

SQL Alias

You can give a table or a column another name by using an alias

Syntax 


SELECT column_name AS alias_name
FROM table_name AS alias_name


The "custmast" table


custcode
custname
city
qty
rate
1
Siva
Srivilliputtur
50
70
2
Bala
Sivakasi
30
56
3
Kanna
Madurai
100
200
4
Vijay
Chennai
30
30
5
Kodee
Sandnes
60
80
5
Kodee
Srivilliputtur
60
30



Example 

  Select cm.custcode as Ccode,cm.custname as Cname from custmast as cm where cm.custcode=1


Output



Ccode
Cname
1
Siva

Followers

Powered by Blogger.