Which are the join types in join condition answer choices select only one option Cross join Natural join left join all of the mentioned?

Last update on August 19 2022 21:50:45 (UTC/GMT +8 hours)

We have already learned that an EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as comparison operator in the where clause to refer equality.

Table of Contents Show

Difference between natural join and inner join
What is a SQL join?
SQL join types
SQL inner join
SQL self join
SQL cross join
SQL outer join

The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.

Pictorial presentation of the above SQL Natural Join:

Natural Join: Guidelines

- The associated tables have one or more pairs of identically named columns. - The columns must be the same data type.

- Don’t use ON clause in a natural join.

Syntax:

SELECT * FROM table1 NATURAL JOIN table2;

Example:

Here is an example of SQL natural join between tow tables:

Sample table: foods

Sample table: company

To get all the unique columns from foods and company tables, the following SQL statement can be used:

SQL Code:

SELECT * FROM foods NATURAL JOIN company;

Output:

COMPANY_ID ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_NAME COMPANY_CITY ---------- ---------- ------------------------- ---------- ------------------------- -------------- 16 1 Chex Mix Pcs Akas Foods Delhi 15 6 Cheez-It Pcs Jack Hill Ltd London 15 2 BN Biscuit Pcs Jack Hill Ltd London 17 3 Mighty Munch Pcs Foodies. London 15 4 Pot Rice Pcs Jack Hill Ltd London 18 5 Jaffa Cakes Pcs Order All Boston

Difference between natural join and inner join

There is one significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. See the following example on company table and foods table :

SQL Code:

SELECT * FROM company;

Output:

COMPANY_ID COMPANY_NAME COMPANY_CITY ---------- ------------------------- --------------- 18 Order All Boston 15 Jack Hill Ltd London 16 Akas Foods Delhi 17 Foodies. London 19 sip-n-Bite. New York

SQL Code:

SELECT * FROM foods;

Output:

ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID ---------- ------------------------- ---------- ---------- 1 Chex Mix Pcs 16 6 Cheez-It Pcs 15 2 BN Biscuit Pcs 15 3 Mighty Munch Pcs 17 4 Pot Rice Pcs 15 5 Jaffa Cakes Pcs 18 7 Salt n Shake Pcs

The INNER JOIN of company and foods on company_id will return :

SQL Code:

SELECT * FROM company INNER JOIN foods ON company.company_id = foods.company_id;

Output:

COMPANY_ID COMPANY_NAME COMPANY_CITY ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID ---------- --------------- --------------- ---------- --------------- ---------- ---------- 16 Akas Foods Delhi 1 Chex Mix Pcs 16 15 Jack Hill Ltd London 6 Cheez-It Pcs 15 15 Jack Hill Ltd London 2 BN Biscuit Pcs 15 17 Foodies. London 3 Mighty Munch Pcs 17 15 Jack Hill Ltd London 4 Pot Rice Pcs 15 18 Order All Boston 5 Jaffa Cakes Pcs 18

SQL Code:

SELECT * FROM company NATURAL JOIN foods;

Output:

COMPANY_ID COMPANY_NAME COMPANY_CITY ITEM_ID ITEM_NAME ITEM_UNIT ---------- --------------- --------------- ---------- --------------- ---------- 16 Akas Foods Delhi 1 Chex Mix Pcs 15 Jack Hill Ltd London 6 Cheez-It Pcs 15 Jack Hill Ltd London 2 BN Biscuit Pcs 17 Foodies. London 3 Mighty Munch Pcs 15 Jack Hill Ltd London 4 Pot Rice Pcs 18 Order All Boston 5 Jaffa Cakes Pcs

NATURAL JOINS: Relational Databases

Oracle NATURAL JOIN
MySQL NATURAL JOIN
SQLite NATURAL JOIN

Key points to remember

Click on the following to get the slides presentation -

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL INNER JOIN
Next: SQL CROSS JOIN

Get table column names in MySQL?

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Ref : https://bit.ly/3T6wMPf

This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.

The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.

The aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.

What is a SQL join?

A SQL Join is a special form of generating a meaningful data by combining multiple tables relate to each other using a “Key”. Typically, relational tables must be designed with a unique column and this column is used to create relationships with one or more other tables. When you need a result-set that includes related rows from multiple tables, you’ll need to use SQL join on this column

The various SQL join types are as follows

SQL inner join
1. Equi join
2. Non-equi join (Theta join)
SQL outer join
1. SQL left join or left outer join
2. SQL right join or right outer join
3. SQL full join or full outer join
SQL cross join
SQL self join

Note: The keyword outer is optional. It means you can specify the keyword “outer” or not makes no difference to the query execution.

For example,

SQL join types

SQL inner join

The simplest and most common form of a join is the SQL inner join the default of the SQL join types used in most database management systems. It’s the default SQL join you get when you use the join keyword by itself.

The result of the SQL inner join includes rows from both the tables where the join conditions are met.

Syntax:

SELECT ColumnList from LeftTable L

Note: It is very easy to visualize a join query as a Venn diagram, where each of the tables is represented by intersecting shapes. The intersection of the shapes, where the tables overlap, are the rows where a condition is met. Unique columns (ID) are often used for this purpose, where the condition to be met is matching the ids of rows.

Equi join:

An equi join is the most common form of SQL inner join used in practice. If the join contains an equality operator e.g. =, then it’s an equi-join.

The following example returns all matching state names and stateProvinceIDs.

SELECT DISTINCT A.StateProvinceID,S.Name

inner join Person.StateProvince S

On A.StateProvinceID=S.StateProvinceID

Theta join (Non-equi join):

In general, this a Theta join used to specify operators or conditions (the ON clause in SQL). In practice, this is a rarely used SQL join types. In most cases, the join will use a non-equality condition e.g. >

SELECT p1.FirstName, p2. FirstName

INNER join PErson.Person p2

ON len(p1.FirstName) > len(p2.FirstName);

SQL self join

A SQL Self join is a mechanism of joining a table to itself. You would use a self join when you wanted to create a result set joining records in the table with some other records from the same table.

For a SQL self join example, consider an Employee table where managers are listed because they are also employees, and we would like to take a look at a result set that returns all of the employees and indicating who their managers are

SELECT e.ename, e.empno, m.ename as manager, e.mgr

SQL cross join

A CROSS join returns all rows for all possible combinations of two tables. It generates all the rows from the left table which is then combined with all the rows from the right table. This type of join is also known as a Cartesian product(A*B).

For example, if the left table has 100 rows and the right table has 100 then the cross join result will yield 10,000 rows.

SELECT e.BusinessEntityID, d.Name AS Department

FROM HumanResources.Employee AS e

CROSS join HumanResources.Department AS d

SQL outer join

On joining tables with a SQL inner join, the output returns only matching rows from both the tables. When using a SQL outer join, not only it will list the matching rows, it will also list the unmatched rows from the other tables.

A SQL left outer join will return all the records from the left table in the join clause, regardless of matching records in the right table. The left SQL outer join includes rows where the condition is met plus all the rows from the table on the left where the condition is not met. Fields from the right table with no match will be displayed as null values.

Syntax:

SELECT ColumnList from LeftTable L

The following example joins two tablesProduct and SalesOrderDetail on ProductID and preserves the unmatched rows from the left table. The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and not ordered, appear in the result set.

SELECT p.Name, so.SalesOrderID

FROM Production.Product p

LEFT OUTER join Sales.SalesOrderDetail so

ON p.ProductID = so.ProductID

A right outer join will return all the records in the right table in the join clause, regardless of matching records in the left table. Using the right SQL outer join includes all the rows from the table on the right. The right SQL outer join is considered a special case and many databases don’t support right joins. Generally, a SQL right join can be rewritten as a SQL left join by simply changing the order of the tables in the query. In this instance, fields from the left table with no match will display null values

Syntax:

SELECT ColumnList from LeftTable L

The following example joins two tables on TerritoryID(SalesTerritory) and preserves the unmatched rows from the right table(SalesPerson). The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each table. All salespersons appear in the result set, whether or not they are assigned a territory.

SELECT s.Name AS Territory, p.BusinessEntityID

FROM Sales.SalesTerritory s

RIGHT OUTER join Sales.SalesPerson p

ON s.TerritoryID = p.TerritoryID ;

A SQL outer join, as you might expect by now, will return all the rows in both tables. When rows don’t have a match in one of the tables, the field will display a null value. A full SQL outer join combines the effects of the SQL left joins and SQL right joins. Many databases do not support the implementation of full SQL outer joins

Syntax:

SELECT ColumnList from LeftTable L

FULL OUTER join RightTable R

The following example returns the name of the product name any corresponding sales orders in the SalesOrderDetail table from the AdventureWorks2014 database. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.

SELECT p.Name, s.SalesOrderID

FROM Production.Product p

FULL OUTER join Sales.SalesOrderDetail s

ON p.ProductID = s.ProductID

Summary

In this article, we’ve discussed most of the important aspects of SQL Joins and covered a variety of SQL join types. We’ve also demonstrated a few quick examples and samples of how we can pull data from related tables from the Adventureworks2016 database and how those tables actually get that relationship through the use of those keys using SQL joins.

That’s all for now. I hope you enjoyed this article on SQL Join types. Feel free ask any questions in the comments below.