Quality Testing

Quality is delighting customers

Hi

What is join and their types ? also  difference between Join and Union? Explain with example.


Thanks
Hitesh Shah

Views: 624

Reply to This

Replies to This Discussion

Hi Hitesh Shah
the below links will explain about the Joins and Union with examples

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_union.asp

if u any plz let me know
Please find the attachment
Attachments:
Hi

Thanks for good attachment but I want short reply which we can speak in Interview .
I mean difference Join and Union ?
byeeee

Hitesh Shah
Hi Hitesh
u can say simply

SQL joins : are used to query data from two or more tables, based on a relationship between certain columns
•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 Unions :combines two or more SELECT statements.
Hi,

Thanks for good & simple explain .

Hitesh Shah
SQL JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn10 Sandnes
2 Svendson Tove Borgvn23 Sandnes
3 Pettersen Kari Storgt20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the persons with any orders.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678


SQL UNION Example


"Employees_Norway":

E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

"Employees_USA":

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen


Hope this helps.
Regards,
Smita
Hi Smita,
Very Gud answer with easy examples.
thanks for sharing
Hi,

Nice good example , its very helpful for understanding.

Hitesh Shah

RSS

TTWT Magazine


Advertisement

Advertisement

Advertisement

Advertisement

© 2020   Created by Quality Testing.   Powered by

Badges  |  Report an Issue  |  Terms of Service