Interested to Buy Any Domain ? << Click Here >> for more details...
What are the all different types of Joins in SQL Server
2000, Anybody can explain each join with definition..Thanks
in advance....
- 9 Answers
- 63703 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 are views in ms sql server?
What is the difference between DataRow.Delete() and DataRow.Remove()?
Can you create a logon trigger in sql server 2005 express edition?
Thanks to some maintenance being done, the sql server on a failover cluster needs to be brought down. How do you bring the sql server down?
How we can refresh the view?
What method is used by the Command classes to execute SQL statements that return single values?
What are information schema views?
Explain about Normalization?
Write a sql query to delete duplicate records from a table called table1
Explain what is analysis service repository?
What is subquery explain with example?
What is constraints and its types?
Explain alternate key, candidate key and composite key in sql server?
What are data resources?
I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? : Sql server database administration
