Answer / siva prasad

In SQL Server 2000 we have three types of joins
1. Inner join
2. Outer Join
3. Cross Join
1. Inner Join: Inner Join is the default type of join, it
will producesses the result set, which contains matched
rows only.
syntax: select * from table1<innerjoin>table2

2. Outer Join: Outer join produces the results, which
contains matched rows and unmatched rows.
here we have three types of joins,
1.Left Outer Join 2.Right Outer Join 3.Full Outer Join
Left Outer Join: Left Outer Join producesses the results,
which contains all the rows from Left table and matched
rows from Right Table.
syntax: select * from table1<leftouterjoin>table2

Right Outer Join: Right Outer Join producesses the
resultset, which contains all the rows from right table and
matched rows from left table.
syntax:select * from table1<right outer join>table2

Full Outer Join: Full Outer Join producesses the resultset,
which contains all the rows from left table and all the
rows from right table.
syntax:select * from table1<fullouterjoin>table2

3.Cross Join: A join without having any condition is known
as Cross Join, in cross join every row in first table is
joins with every row in second table.
syntax: select * from table1<cross join>table2

Self Join: A join joins withitself is called self join
working with self joins we use Alias tables.

Answer / 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

Answer / j.jyothy

There are 1.equijoins 2.self joins,3.Crossjoins.
4.Outerjoins->Leftouterjoin,Rightouterjoin,Fullouterjoin.
1.EquiJoins:They are also called innerjoins,In this only
matched rows are displayed to the user.
Example:
select eno,ename,sal,e.deptno,d.deptno,dname,loc from emp e
innerjoin dept d on e.deptno=d.deptno
2.Selfjoin:if the table is joined with table itself.
select distinct (e1.empno),e1.ename,e1.sal from emp e1 join
emp e2 on e1.sal=2* e2.sal(It dispalys emp deatails salary
exactly 2 times with any other emp salary)

Answer / raji

Different Types Of JOINS:
Most of the joins you will come across are based on
equality, with the equijoin being the most dominant.
In this chapter you learned about equijoins; there are
other types of joins you must become familiar with, most
notably the self-join, the nonequijoin, and the outer join.

Equijoin or Inner Join (Equality) : Traditional comma-
separated join or ANSI JOIN syntax (including optional
INNER keyword).
Natural Join (Equality) : NATURAL JOIN keyword.
Cross-Join or Cartesian Product (No join condition):
Traditional comma-separated with the missing join condition
in the WHERE clause or CROSS JOIN keyword.
Self-Join (Equality): Equijoin or Inner Join.
Outer Join (left, right, full):(Equality and extending the
result set): "Complex Joins" OUTER JOIN keywords or outer
join operator(+).
Non-Equijoin (Nonequality of values): "Complex Joins"
Traditional comma-separated join or ANSI join syntax with
the ON clause.
The join criteria is not based on equality.

Answer / j.jyothy

Already i explained 3 joins now i tell u Outerjoins.
leftouterjoin:It will display all the rows of left table
irrespective of whether there is a match in the right or
not.If there is no match in the right table then the null
row is aasumed and it is displayed in the output.

Display all emps in the emptable.
select empno,ename,esal,e.deptno,d.deptno,dname,loc from
emp e left outerjoin dept d on e.deptno=d.deptno

RightOuterJoin:It will display all the rows of right table
irrespective of whether there is a match in the right or
not.If there is no match in the Left table then the null
row is aasumed and it is displayed in the output

Example
select empno,ename,esal,e.deptno,d.deptno,dname,loc from
emp e right outerjoin dept d on e.deptno=d.deptno

Answer / piyush bajaj

I have posted a sequel article relating to this topic at:

SQL Server - How to Merge Data with JOINS? – PART 1
http://www.sqlservergeeks.com/articles/sql-server-bi/35/sql-server-how-to-merge-data-with-joins-%E2%80%93-part-1#78

SQL Server - How to Merge Data with JOINS? – PART 2
http://www.sqlservergeeks.com/articles/sql-server-bi/36/sql-server-how-to-merge-data-with-joins-%E2%80%93-part-2

SQL Server - How to Merge Data with JOINS? – PART 3
http://www.sqlservergeeks.com/articles/sql-server-bi/37/sql-server-how-to-merge-data-with-joins-%E2%80%93-part-3

Answer / wiki man

http://en.wikipedia.org/wiki/Join_(SQL)

Answer / unique fellow

Introduction

In this article, we’ll see the basic concepts of SQL
JOINs. In the later part of the article, we’ll focus on the
advanced subject of Self-JOIN and some interesting
observations on how inner JOIN can be simulated using left
JOIN. The author has tried his best to amalgamate various
topics in a single concept.

The JOIN keyword is used in a SQL statement to query data
from two or more tables based on a relationship between
certain columns in these tables.
Inner JOIN

A JOIN that displays only rows that have a match in both the
JOINed tables is known as inner JOIN. This is the default
type of JOIN in the Query and View Designer.

Outer JOIN

A JOIN that includes rows even if they do not have related
rows in the joined table is an Outer JOIN. You can create
three different outer JOINs to specify the unmatched rows to
be included:

Left Outer JOIN: In Left Outer JOIN, all rows in the
first-named table, i.e. “left” table, which appears leftmost
in the JOIN clause, are included. Unmatched rows in the
right table do not appear.

Right Outer JOIN: In Right Outer JOIN, all rows in the
second-named table, i.e. “right” table, which appears
rightmost in the JOIN clause, are included. Unmatched rows
in the left table are not included.

Full Outer JOIN: In Full Outer JOIN, all rows in all the
joined tables are included, whether they are matched or not.

Additional Notes related to JOIN

The following are three classic examples to demonstrate the
cases where Outer JOIN is useful. You must have noticed
several instances where developers write query as given below.
view sourceprint?
1.SELECT t1.*
2.FROM Table1 t1
3.WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
4.GO

The query demonstrated above can be easily replaced by Outer
JOIN. Indeed, replacing it by Outer JOIN is the best
practice. The query that generates the same result as above
is shown here using Outer JOIN and WHERE clause in JOIN.
view sourceprint?
1./* LEFT JOIN - WHERE NULL */
2.SELECT t1.*,t2.*
3.FROM Table1 t1
4.LEFT JOIN Table2 t2 ON t1.ID = t2.ID
5.WHERE t2.ID IS NULL

The above example can also be created using Right Outer JOIN.

NOT Inner JOIN

Remember, the term Not Inner JOIN does not exist in database
terminology. However, when full Outer JOIN is used along
with WHERE condition, as explained in the above two
examples, it will give you exclusive result to Inner JOIN.
This JOIN will show all the results that were absent in
Inner JOIN.

Cross JOIN

A cross JOIN devoid of a WHERE clause produces the Cartesian
product of the tables involved in the JOIN. The size of a
Cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second
table. One common example is when a company lists all its
products in a pricing table to compare each product with
others prices.

Self-JOIN

In this particular case, one table JOINs to itself with one
or two aliases to stave off confusion. A self-JOIN can be of
any type, as long as the joined tables are the same. A
self-JOIN is unique in the sense that it involves a
relationship with only one table. A common example is when a
company has a hierarchal reporting structure whereby a
member of staff reports to another member. Self-JOIN can
either be an Outer JOIN or an Inner JOIN.

Self-JOIN is accomplished by using table name aliases to
give each instance of the table a separate name. Joining a
table to itself can be useful when you want to compare
values in a column to other values of the same column.
Self-JOIN is a JOIN in which records from a table are
combined with other records from the same table when there
are matching values in the joined fields. A self-JOIN can
either be an inner JOIN or an outer JOIN. A table is joined
to itself based upon a field or combination of fields that
have duplicate data in different records. The data type of
the inter-related columns must be of the same type or cast
to the same type.

Now, think of a situation where all the data you require is
contained within a single table, but data needed to extract
is related to each other in the table itself. Examples of
this type of data relate to employee information, where the
table may have both an employee’s ID number for each record
and also a field that displays the ID number of an
employee’s supervisor or manager. To retrieve the data, it
is mandatory for the tables to relate/JOIN to itself.

Another example that can be tried on SQL SERVER 2005 sample
database AdventureWorks is to find products that are
supplied by more than one vendor. Please refer to the sample
database for table structure.
Note:

Before we continue further let me make it very clear that
INNER JOIN should be used where it cannot be used and
simulating INNER JOIN using any other JOINs will degrade the
performance. If there are scopes to convert any OUTER JOIN
to INNER JOIN, it should be done with priority.

Run the following two scripts and observe the result-set. It
will be identical.
view sourceprint?
01.USE AdventureWorks
02.GO
03./* Example of INNER JOIN */
04.SELECT p.ProductID, piy.ProductID
05.FROM Production.Product p
06.INNER JOIN Production.ProductInventory piy ON
piy.ProductID = p.ProductID
07.GO
08./* Example of LEFT JOIN simulating INNER JOIN */
09.SELECT p.ProductID, piy.ProductID
10.FROM Production.Product p
11.LEFT JOIN Production.ProductInventory piy ON 1 = 1
12.WHERE piy.ProductID = p.ProductID
13.GO

After gazing at the identical result the first question that
cropped up in my mind was - what is behind the scene plan?
Looking at the actual execution plan of the query it is
quite evident that even if LEFT JOIN is used in SQL Server
Query Optimizer, it converts to INNER JOIN since results are
the same and performance is better.

Looking at the above scenario it makes me ponder how smart
Query Optimizer Engine is and how it might be saving
innumerable performance-related issues for sub-optimal queries.

Now let us try to grasp the cause of LEFT JOIN acting as
INNER JOIN. When 1= 1 is used in ON clause it is always true
and converts LEFT JOIN to CROSS JOIN. However, when WHERE
condition’s effect is applied to the above CROSS JOIN it
produces a result similar to INNER JOIN in our case. SQL
Server Query Optimizer interprets this in advance and uses
INNER JOIN right away.

Answer / prangya mishra

joins are used for retreiving the information from multiple
tables at a time & presenting it.
we have 5 different types of joins
1.Equi join
2.Non-Equi join
3.Self join
4.Cartesian join
5.outer join

How to Update from select query in sql server?

0 Answers  

How to get a list of columns using the "sys.columns" view in ms sql server?

0 Answers  

What is replication and database mirroring?

0 Answers  

How to delete exactly duplicate records from a table?

0 Answers  

Explain what are the restrictions while creating batches in sql server?

0 Answers  

What is inner join in sql server joins?

0 Answers  

What is the Main Difference between ACCESS and SQL SERVER?

0 Answers   MindCracker,

Mention the different authentication modes in sql server.

0 Answers  

How to convert numeric values to integers in ms sql server?

0 Answers  

What is data block and how to define data block size?

0 Answers  

What are “lost updates”?

0 Answers  

How to use user defined functions in expressions?

0 Answers