- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Environmental Science
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is join in SQL ?
Joins are used to combine rows from two or more tables, based on a related column between them.
Types of Joins:
• INNER JOIN − Returns rows when there is a match in both tables.
• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.
• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.
• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.
• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
• CARTESIAN JOIN (CROSS JOIN) − Returns the Cartesian product of the sets of records from the two or more joined tables.
INNER JOIN:
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.
SYNTAX :
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;
LEFT JOIN:
The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
SYNTAX :
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield;
RIGHT JOIN:
The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
SYNTAX :
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;
FULL OUTER JOIN:
The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
SYNTAX :
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Left JOIN table2
ON table1.commonfield = table2.commonfield;
Union
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Right JOIN table2
ON table1.commonfield = table2.commonfield;
SELF JOIN:
The SELF JOIN joins a table to itself; temporarily renaming at least one table in the SQL statement.
SYNTAX:
SELECT a.col1, b.col2,..., a.coln
FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;
- Related Questions & Answers
- How to insert date in SQL ?
- What is OLTP ?
- What is a schema in SQL ?
- What is SQL injection ?
- How to insert multiple rows in SQL ?
- How to delete a row in SQL ?
- How to change a table name in SQL ?
- How can I see all tables in SQL?
- How to delete a column in SQL ?
- What is SQL server ?
- What are the types of SQL Queries ?
- What is Normalization in SQL ?
- How to create a database in SQL?
- What is Primary Key in SQL ?
- How to Create a Table in SQL – Postgres and MySQL Example Query
- What is join in SQL ?
ads