What are the all different types of Joins in SQL Server
2000, Anybody can explain each join with definition..Thanks
in advance....
- 9 Answers
- 60674 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
What is the current pricing model of SQL Azure?
How do I get to sql server configuration manager?
Why would you call update statistics?
What is page-level compression?
What is exclusive locks?
What is “asynchronous” communication in sql server service broker?
How to generate random numbers with the rand() function in ms sql server?
What is a transact-sql statement batch in ms sql server?
What are ddl (data definition language) statements for tables in ms sql server?
Can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible?
What is the purpose of the tempdb database?
What is 1nf 2nf and 3nf?
How to convert a unicode strings to non-unicode strings?
What are scalar functions?
what is a major difference between sql server 6.5 and 7.0 platform wise? : Sql server database administration
