DATABASE & SQL/PLSQL

adplus-dvertising
Joins
Previous Home Next

SQL server provide:

A method to retrieving a data from more than one table using join at a time.

Implemented using SELECT statement, in which the SELECT statement contain the name of columns to be retrieve from the tables.

The FROM clause contains the name of the tables from which combined data is to be retrieved.

The WHERE specifies the rows to be included in the result set with the help of the join operator.

Syntax:

SELECT  column name 1, column name 2....column_name n
FROM  table name [CROSS, INNER, OUTER]  JOIN  table_name
ON  [table_name.ref_column_name]  join_operator  [table_name.ref_column_name]
WHERE  search_condition

column_name specifies the name of the columns from one or more than one table that has to be displayed.

table_name specifies the name of the tables from which data to be retrieve.

ref_column_name specifies the name of the columns that are used to combined the two tables using the common keys from the respective tables.

join_operator specifies the operator used to join the tables

When two tables are joined, they must share a common key that defines how the rows in the tables correspond to each other. A primary key is validated against the foreign key when a joined is used.

Whenever a column is referred to in a join condition, it should be referred to either by prefixing it with the table name to which it belongs or by a table alias.

Table Alias: A table alias is required whenever an ambiguity is possible due to duplicate column names in a multiple tables A tables alias is a keyword defined in the FROM clause of the SELECT statement to uniquely identify the table.

Syntax:

FROM table_name table_alias

Where,

table_name specifies the name of the tables that have to be combined in the query

table_alias is the keyword used to refer to a table. It must follow the rules of identifiers

Types of JOIN

Inner Join

Outer Join

Cross Join

Equi Join

Natural Join

Self Join

Previous Home Next