SQL Server what is Join and what is INNER join
SQL Server what is Join and what is INNER join:
Past Some time when we work in my blog then we got a
question related to the sql server the question is
“I am new to SQL Server and want to learn about the JOIN
options. What are all of the JOIN options in SQL Server? What is the
significance of each of the options? I am a little confused on the differences
and syntax, can you provide some examples and explanations? Are JOINs only for
SELECT statements? Check out this tip to
learn about SQL Server Joins.”
what is Join and what is INNER join:
Joining tables to obtain the needed data for a query, script
or stored procedure is a key concept as you learn about SQL Server development.
In a nutshell, joins are typically performed in the FROM clause of a table or
view for the SELECT,INSERT...SELECT, SELECT...INTO, UPDATE and DELETE
statements. In previous versions of SQL Server, join logic could also have been
included in the WHERE clause with = (INNER JOIN), *= (LEFT OUTER JOIN), =*
(RIGHT OUTER JOIN), etc. syntax, but the support has been reduced and the best
practice in SQL Server is to use the syntax outlined in the examples below.
INNER JOIN - Match rows between the two tables specified in
the INNER JOIN statement based on one or more columns having matching
data. Preferably the join is based on
referential integrity enforcing the relationship between the tables to ensure
data integrity.
Just to add a little commentary to the basic definitions
above, in general the INNER JOIN option is considered to be the most common
join needed in applications and/or queries.
Although that is the case in some environments, it is really dependent
on the database design, referential integrity and data needed for the
application. As such, please take the
time to understand the data being requested then select the proper join option.
Although most join logic is based on matching values between
the two columns specified, it is possible to also include logic using greater
than, less than, not equals, etc.
LEFT OUTER JOIN - Based on the two tables specified in the
join clause, all data is returned from the left table. On the right table, the matching data is
returned in addition to NULL values where a record exists in the left table,
but not in the right table.
Another item to keep in mind is that the LEFT and RIGHT
OUTER JOIN logic is opposite of one another.
So you can change either the order of the tables in the specific join
statement or change the JOIN from left to right or vice versa and get the same
results.
RIGHT OUTER JOIN - Based on the two tables specified in the
join clause, all data is returned from the right table. On the left table, the matching data is
returned in addition to NULL values where a record exists in the right table
but not in the left table.
Self -Join - In this circumstance, the same table is
specified twice with two different aliases in order to match the data within
the same table.
CROSS JOIN - Based on the two tables specified in the join
clause, a Cartesian product is created if a WHERE clause does filter the
rows. The size of the Cartesian product
is based on multiplying the number of rows from the left table by the number of
rows in the right table. Please heed
caution when using a CROSS JOIN.
FULL JOIN - Based on the two tables specified in the join
clause, all data is returned from both tables regardless of matching data.
Nice informative blog, thanks for posting. Recently I got an informative blog like you on hosting tips along with the place to get best web hosting options. Please Keep posting this type of blog.
ReplyDelete