What are the all different types of Joins in SQL Server
2000, Anybody can explain each join with definition..Thanks
in advance....
- 9 Answers
- 60729 Views
- DELL, i Tech, Infosys, Siemens, TCS, I also Faced
- E-Mail Answers
Answer Posted / amit upadhyay
There are six type of join in SQL 2000
1) INNER JOIN
2) OUTER JOIN
3) CROSS JOIN
4) EQUI JOIN
5) NATURAL JOIN
6) SELF JOIN
1) INNER JOIN :- PRODUCESS THE RESULT SET OF MATCHING ROWS
ONLY FROM THE SPECIFIED TABLES.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
2) OUTER JOIN :- DISPLAY ALL THE ROWS FROM THE FIRST TABLE
AND MATCHING ROWS FROM THE SECOND TABLE.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME OUTER JOIN
2ND_TABLE_NAME
ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
THERE ARE THREE TYPES OF OUTER JOIN:
A)LEFT OUTER JOIN.
B)RIGHT OUTER JOIN.
C)FULL OUTER JOIN
A)LFET OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE FIRST
TABLE AND MATCHING ROWS FROM THE
SECOND TABLE.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME LEFT OUTER JOIN
2ND_TABLE_NAME ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
A)RIGHT OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE
SECOND TABLE AND MATCHING ROWS FROM
THE FIRST TABLE.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME RIGHT OUTER JOIN
2ND_TABLE_NAME ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
A)FULL OUTER JOIN :- DISPLAYS ALL MATCHING AND NONMATCHING
ROWS OF BOTH THE TABLES.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME FULL OUTER JOIN
2ND_TABLE_NAME ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
3)CROSS JOIN :- IN THIS TYPE OF JOIN, EACH ROWS FROM THE
JOIN WITH EACH ROWS FROM THE SECOND TABLE
WITHOUT ANY CONDTION.
ALSO CALLED AS CARTESIAN PRODUCT.
EXAMPLE---
SELECT COLUMN_LIST FROM 1ST_TABLE_NAME CROSS JOIN
2ND_TABLE_NAME
4) EQUI JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM JOINED
TABLE. AND ALSO DISPLAYS REDUNDANT VALUES.
IN THIS WE USE * SIGN TO JOIN THE TABLE.
EXAMPLE---
SELECT * FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN
5)NATURAL JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM
JOINED TABLE.IT RESTRICT
REDUNDANT VALUES.
6)SELF JOIN :- IN THIS TABLE JOIN WITH ITSELF WITH
DIFFERENT ALIAS NAME.
ASSUME DEPARTMENT IS A TABLE:
SELECT A.DEP_NAME,B.MANAGER_ID(COLUMN LIST) FROM DEPARTMENT
A JOIN
DEPARTMENT B
ON A.MANAGER_ID=B.MANAGER_ID
| Is This Answer Correct ? | 68 Yes | 15 No |
Post New Answer View All Answers
you have developed an application which uses many stored procedures and triggers to update various tables users ocassionally get locking problems which tool is best suited to help you diagnose the problem? : Sql server administration
What is history table in sql server?
How do I edit a stored procedure in sql server?
Mention the 3 ways to get a count of the number of records in a table.
what are the core components of SSRS?
What is a functions and types in sql server?
What are the types of subscriptions in SQL Server replication?
What is the use of builtinadministrators group in sql server? : sql server security
what are triggers? : Sql server database administration
How to update muliple row in single query?
Explain syntax for dropping triggers?
Mention the different types of triggers?
Is a primary key unique?
Is sql server free?
How optimize sql query with multiple joins in sql server?
