SQL Joins

August 3, 2016 by Arti Khedkar

Filed under Database, SQL

Last modified October 25, 2016

SQL Joins

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.

SQL is a special-purpose programming language designed for managing information in a relational database management system (RDBMS). The word relational here is key, it specifies that the database management system is organized in such a way that there are clear relations defined between different sets of data.

Let’s look at a selection from the “Orders” table:

 

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

 

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS, ORDERS

WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

 

Types of Joins:

 

SQL JOINS

 

 

  • INNER JOIN

    : Select records that have matching values in both tables.

 

inner join

inner join

 

 

SQL INNER JOIN Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2
ON table1.column_name=table2.column_name;

  • LEFT JOIN

    : Select records from the first (left-most) table with matching right table records.

 

LEFT JOINS

 

 

                   SQL LEFT JOIN Syntax

                           SELECT first_name, last_name, order_date,                                           order_amount FROM customers c LEFT JOIN orders
ON c.customer_id = o.customer_id;

  • RIGHT JOIN

    : Select records from the second (right-most) table with matching left table records.

RIGHT JOIN

 

                SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

  • FULL JOIN

    : Selects all records that match either left or right table records. Returns rows when there is a match in one of the tables.

FULL JOIN

 

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name= table2.column_name;

 

  • SELF JOIN

    : Is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

 

                 SQL SELF JOIN Syntax

SELECT E1.NAME, E2.NAME
FROM EMP E1, EMP E2
WHERE E1.MGR_ID = E2.ID;

 

 

 

 

 

Leave a Comment