Sql Joins And How To Join?

Hi, You may come here to get a tutorial on Join like different types of join and how to use it, but I wanted to tell you that I will cover that topic in future and you can find very good tutorials elsewhere.

Over here I am going to illustrate different ways to join different tables. My intention for this post is to let programmers know about a few missed topics(maybe not).

Let get into the topic
There are 3 different ways to join tables:

1. JOIN-ON Keyword
2. USING Keyword
3. WHERE Keyword

Yes, there are 3 different ways. I will go one by one with an example.

1. JOIN-ON

We can join 2 tables using the Join-On keywords. It is considered as the standard way and most common way. In this case, we take two tables and place join in between them and use on later where we have to mention on which columns it is joining. This means where you can find similar records in 2 tables.

NOTE: you can join a table to itself. (Yes, it is possible and it is not a JOKE.)

Example:

SELECT * FROM employees e JOIN department d ON e.department_id = d.department_id;

SELECT * FROM employees e JOIN employees d ON e.employee_id = d.manager_id;

1. USING

Now we will join tables where the column name is the same in these different tables. This is the smart A$$ and easy feature in Oracle SQL. For instance, employees table and department table both has department_id column.

Example:
SELECT * FROM employees JOIN department USING department_id;

It will print the same as from the first example from JOIN-ON.

1. WHERE

Simple and Beautiful, here you can join tables using WHERE clause in which you simply mention about the columns which are equal to each other.

Examples:

SELECT * FROM employees e JOIN department d WHERE e.department_id = d.department_id;

SELECT * FROM employees e JOIN employees d WHERE e.employee_id = d.manager_id;

These examples will fetch the same results as the JOIN-ON examples.

Please use relevant example to practice and let me know if you find any other way.