What is SQL Join?
A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are listed in a SQL statement.
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
Different Types of SQL Joins:
Here are the different types of the JOINs in SQL:
- SQL INNER JOIN (sometimes called simple join):
- SQL LEFT OUTER JOIN (sometimes called LEFT JOIN):
- SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN):
- SQL FULL OUTER JOIN (sometimes called FULL JOIN):
- SELF JOIN
- CROSS JOIN OR CARTESIAN JOIN
Basic SQL JOIN Syntax
SELECT a.table1_column1, a.table1_column2, b.table2_column1
FROM table1 a
JOIN table2 b
ON a.table1_column1=b.table2_column2
WHERE (Condition)
ORDER BY a.table1_column1 DESC
What are PRIMARY KEY and FOREIGN KEY?
PRIMARY KEY: Uniquely identified each rows/records in a database table.
FOREIGN KEY: A FOREIGN KEY is used to maintain relationship between two tables. It is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Please refer the below video on basics of SQL Joins :
We will take the example of below tables:
SQL INNER JOIN
Returns records that have matching values in both tables.
Returns rows when there is a match in both tables.
Returns all rows from the left table, even if there are no matches in the right table.
This type of join returns all rows from the LEFT hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.
Returns all records from the left table, and the matched records from the right table.
SQL RIGHT OUTER JOIN
Returns all rows from the right table, even if there are no matches in the left table.
Returns all records from the right table, and the matched records from the left table.
SQL FULL OUTER JOIN
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
SQL SELF JOIN
The self join, as its name implies, joins a table to itself.
Self Join is used to join a table to itself as if the table were two tables.
SQL SELF JOIN Syntax
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
SELECT a.column_name, b.column_name...
FROM table1 a
JOIN table1 b
ON a.common_field = b.common_field
SQL CARTESIAN JOIN OR CROSS JOIN
Returns the Cartesian product of the sets of records from the two or more joined tables.
The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as Cartesian join.
Real World Example:
Suppose that we are sitting in a restaurant and we decide to order Lunch. Shortly, we will look at the menu and we will start thinking of which meal and drink combination could be more tastier. Our brain will receive this signal and begin to generate all meal and drink combinations.
Syntax:
SELECT ColumnName_1, ColumnName_2, ColumnName_N
FROM [Table_1]
CROSS JOIN [Table_2]
No comments:
Post a Comment