What is SQL?
SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.
What is Join?
In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.
Types of Joins
1) Inner Joins
2) Outer Joins
3) Self Join
Inner Join
The
join that displays only the rows that have a match in both the joined tables is
known as inner join. This is default join in the query and view Designer.
Syntax for Inner Join
|
SELECT
t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN
table_name2 t2
ON
t1.column_name=t2.column_name
|
In
inner Join we are having different types of Joins those are
1) Equi Join
2
2) Natural Join
3) Cross Join
Equi Join
The
Equi join is used to display all the matched records from the joined tables and
also display redundant values. In this join we need to use * sign to join the
table.
Syntax for Equi Join
|
SELECT * FROM table_name1 t1
INNER JOIN
table_name2 t2
ON
t1.column_name=t2.column_name
|
Natural Joins
The
Natural join is same as our Equi join but only the difference is it will
restrict to display redundant values.
Syntax for Natural Join
|
SELECT * FROM table_name1 t1
NATURAL JOIN
table_name2 t2
|
Cross Join
A
cross join that produces Cartesian product of the tables that involved in the
join. The size of a Cartesian product is the number of the rows in first table
multiplied by the number of rows in the second table.
Syntax for Cross Join
|
SELECT * FROM table_name1
CROSS JOIN
table_name2
|
Or
we can write it in another way also
|
SELECT * FROM table_name1,table_name2
|
Outer Joins
A
join that return all the rows that satisfy the condition and unmatched rows in
the joined table is an Outer Join.
We
are having three types of Outer Joins
Left Outer Join
Right Outer Join
Full Outer Join
Left Outer Join
The
left outer join displays all the rows from the first table and matched rows
from the second table.
Syntax for Left Outer Join
|
SELECT Column_List FROM table_name1 t1
LEFT OUTER JOIN
table_name2 t2
ON
t1.column_name=t2.column_name
|
Right Outer Join
The
right outer join displays all the rows from the second table and matched rows
from the first table.
Syntax for Right Outer Join
|
SELECT Column_List FROM table_name1 t1
RIGHT OUTER JOIN
table_name2 t2
ON
t1.column_name=t2.column_name
|
Full Outer Join
Full
Outer Join displays all the matching and non matching rows of both the tables.
Syntax for Full Outer Join
|
SELECT Column_List FROM table_name1 t1
FULL OUTER JOIN
table_name2 t2
ON
t1.column_name=t2.column_name
|
Self Join
Joining
the table itself called self join. Self join is used to retrieve the records
having some relation or similarity with other records in the same table. Here we
need to use aliases for the same table to set a self join between single table
and retrieve records satisfying the condition in where clause.
No comments:
Post a Comment