Natural Join in Database: Retrieving records from more than one table with Natural Joins
By rancidTaste
Natural Join is one of the sql joining. All database supports Natural Joins. SQL Joining is always very necessary and the NATURAL JOIN clause is based on all columns in the two tables that have the same name. For Natural Joins, "NATURAL JOIN" keywords are used. In this page, a brief description of Natural Joins will be discussed. The syntax of Natural Joins, example of Natural Joins will also be discussed clearly. The Natural Join of this tutorial page is applicable to major databases like Oracle, MySQL, Microsoft SQL etc.
What is Natural Join?
When the joining operation is performed based on two or more tables on the same column name, that join is known as Natural Join.
Features of Natural Join
- For Natural Joins, "NATURAL JOIN" keywords are used
- Natural Join clause is based on all columns in the two tables that have the same name
- Natural Join joins tables automatically based on the matching columns from the tables
- Natural Join selects those rows from the two tables that have equal values in all matched columns
- When the columns having the same names have different data types for Natural Join the an error is returned by the join operation
SQL query for retrieving data from those tables
/* natural join */ select studentName,departmentName,departmentID from students natural join departments;
|
Oracle Database SQL Expert 1Z0-047 Exam Q&A + SIM
Current Bid: $7.00
|
|
Lot of 4 Database Books (SQL - Oracle Database Administration - SQL Server 2000)
Current Bid: $9.99
|
|
OCA Oracle Database 11g:SQL Fundamentals I Exam Guide (Exam 1Z0-051)**PDF file**
Current Bid: $2.97
|
Example of Natural Join:
We need two tables to discuss the effect of Natural Join. Suppose, we have two tables: students (to store students information) and departments (to store departments information). students table has two columns: studentName and departmentID. departments table has two columns: departmentID and departmentName.
Both tables has several data and both the tables with sample data are look like the followings:
[Note: You many need to create tables students and departments and need to insert some sample data into those tables. For table creation and data insertion, please use the section Codes to explain this tutorial]
Table: students
studentName
| departmentID
|
|---|---|
A
| 1
|
B
| 2
|
C
| 4
|
E
| NULL
|
Table: departments
departmentID
| departmentName
|
|---|---|
1
| Science
|
2
| Arts
|
5
| Commerce
|
6
| NULL
|
Now, we have two tables with some sample data. We now apply the natural join to see the output.
We would like to find all the students who has valid department name from the above tables.
/* query of natural join */ select studentName,departmentName,departmentID from students natural join departments;
The output of the above SQL code is the following:
Output of Natural Join
studentName
| departmentName
| departmentID
|
|---|---|---|
A
| Science
| 1
|
B
| Arts
| 2
|
Explanation of the example
In the above example, departments table is joined with students table by the common column departmentID. departmentID is common for both the column. If other common columns were present there, the join result will be based on all the common columns.
- For studentName A, departmentID=1 and this is common for both of the table and the output has the result Science and this row is in the output.
- For studentName B, departmentID=2 and this is common for both of the table and the output has the result Arts and this row is in the output.
- For studentName C, departmentID=4 and this is not available to departments table and so this row is not in the output.
- For studentName E, departmentID=NULL and this is not available to departments table and so this row is not in the output.
Codes to explain this tutorial
In this tutorial, we have created two tables: students and departments. Moreover, we have inserted some data into that table. We just define the tables but in our practical life, we need to create those tables and insert data to those tables using SQL. If you are using Oracle or MySQL or Microsoft Database, then you can do this using the following SQL code snippets.
/* Create table students */
create table students(
studentName varchar(15),
departmentID varchar(2)
);
/* Create table departments */
create table departments(
departmentID varchar(2),
departmentName varchar(10)
);
/* Inserting data into students table */
insert into students values ('A','1');
insert into students values ('B','2');
insert into students values ('C','4');
insert into students(studentName) values ('E');
/* Inserting data into departments table */
insert into departments values('1','Science');
insert into departments values('2','Arts');
insert into departments values('5','Commerce');
insert into departments(departmentID) values('6');Sample SQL code for creating the tables and inserting data into those tables for Oracle database
In this tutorial, natural join is explained with example. Natural join joins two tables based on the same column name of both tables. Hope, this tutorial and example strengths your concept of natural join. The tutorial of natural join query, is applicable to all type of databases like Microsoft /Sybase / MySQL /Oracle etc. which supports Natural Join. However, if you have any query or any comment regarding natural join, please mention that in the comment section.
Comments
No comments yet.
