Search Wisdom Axis for specific information

SQL Joins, Inner, Left, Right, Full Outer Joins in Oracle, MySQL, AWS Redshift, Google BigQuery, Snowflake, PostgreSQL, MariaDB, IBM DB2, Sybase, SQL Server and Teradata. ANSI SQL Concepts.

SQL INTERVIEW QUESTIONS - INDEX

 SQL JOINS: INNER, LEFT, RIGHT, FULL OUTER JOINS What is SQL INNER JOINS What is SQL LEFT OUTER JOINS What is SQL RIGHT OUTER JOINS What is SQL FULL OUTER JOINS
See All

SQL Joins: What are the different types of SQL JOINs? Explain with examples.

 The following are the different Types of SQL JOINs:

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN

SQL: INNER JOIN
The ‘Inner Join’ always returns ‘Only Matching Records’ between ‘Two Tables’.

SQL: LEFT (OUTER) JOIN
The ‘Left (Outer) Join’ always returns ‘All the records from LEFT Table’ and ‘Matching Records’ from ‘RIGHT Table’.

SQL: RIGHT (OUTER) JOIN
The ‘Right (Outer) Join’ always returns ‘All the records from RIGHT Table’ and ‘Matching Records’ from ‘LEFT Table’.

SQL: FULL (OUTER) JOIN
The ‘Full (Outer) Join’ always returns ‘All the records from RIGHT Table and LEFT Table’. It is kind of a combination of both LEFT OUTER JOIN and RIGHT OUTER JOIN.

The following is the pictorial view of all the four different joins side-by-side.

SQL Joins Inner Left Right Full Outer Joins
The following are the tables used to explain joins with examples. TABLE – A: customers_table
customer_number customer_name city state country
101 John Miller Los Angeles CA USA
401 David Smith Las Vegas NV USA
501 Ted Williams Chicago IL USA
601 Hamilton Morris New York NY USA
901 Franklin Floyd Austin TX USA
TABLE – B: orders_table
order_number customer_number order_date
1010505 101 2021-06-06
1010506 201 2021-06-06
1010507 501 2021-06-06
1010508 701 2021-06-06
1010509 901 2021-06-06

SQL JOINS: INNER JOIN
"The ‘Inner Join’ always returns ‘Only Matching Records’ between ‘Two Tables’."


The following is the pictorial view of the INNER JOIN:

SQL Inner Join
INNER JOIN Example SQL Code:

SELECT customers_table.customer_number,
customers_table.customer_name,
orders_table.order_numer,
orders_table.order_date
FROM orders_table
INNER JOIN customers_table
ON orders_table.customer_number = customers_table.customer_number;

INNER JOIN Example SQL Code Execution Results:

customer_number customer_name order_number order_date
101 John Miller 10101505 2021-06-06
501 Ted Willimas 10101507 2021-06-06
901 Franklin Floyd 10101509 2021-06-06

SQL JOINS: LEFT (OUTER) JOIN
"The ‘Left (Outer) Join’ always returns ‘All the records from LEFT Table’ and ‘Matching Records’ from ‘RIGHT Table’."


The following is the pictorial view of the LEFT (OUTER) JOIN:


LEFT (OUTER) JOIN Example SQL Code:

SELECT customers_table.customer_number,
customers_table.customer_name,
orders_table.order_numer,
orders_table.order_date
FROM orders_table
LEFT OUTER JOIN customers_table
ON orders_table.customer_number = customers_table.customer_number;

LEFT OUTER JOIN Example SQL Code Execution Results:

customer_number customer_name order_number order_date
101 John Miller 10101505 2021-06-06
401 David Smith
501 Ted Willimas 10101507 2021-06-06
601 Hamilton Morris
901 Franklin Floyd 10101509 2021-06-06

SQL JOINS: RIGHT (OUTER) JOIN
"The ‘Right (Outer) Join’ always returns ‘All the records from RIGHT Table’ and ‘Matching Records’ from ‘LEFT Table’."


The following is the pictorial view of the RIGHT (OUTER) JOIN:

SQL Right Outer Join
RIGHT (OUTER) JOIN Example SQL Code:

SELECT customers_table.customer_number,
customers_table.customer_name,
orders_table.order_numer,
orders_table.order_date
FROM orders_table
RIGHT OUTER JOIN customers_table
ON orders_table.customer_number = customers_table.customer_number;

RIGHT OUTER JOIN Example SQL Code Execution Results:

customer_number customer_name order_number order_date
101 John Miller 10101505 2021-06-06
201 1010506 2021-06-06
501 Ted Willimas 10101507 2021-06-06
701 1010508 2021-06-06
901 Franklin Floyd 10101509 2021-06-06

SQL JOINS: FULL (OUTER) JOIN
"The ‘Full (Outer) Join’ always returns ‘All the records from RIGHT Table and LEFT Table’. It is kind of combination of both LEFT OUTER JOIN and RIGHT OUTER JOIN."


The following is the pictorial view of the FULL (OUTER) JOIN:

SQL Full Outer Join
FULL (OUTER) JOIN Example SQL Code:

SELECT customers_table.customer_number,
customers_table.customer_name,
orders_table.order_numer,
orders_table.order_date
FROM orders_table
FULL OUTER JOIN customers_table
ON orders_table.customer_number = customers_table.customer_number;

FULL OUTER JOIN Example SQL Code Execution Results:

customer_number customer_name order_number order_date
101 John Miller 10101505 2021-06-06
201 1010506 2021-06-06
401 David Smith
501 Ted Willimas 10101507 2021-06-06
601 Hamilton Morris
701 1010508 2021-06-06
901 Franklin Floyd 10101509 2021-06-06