Purpose of joins in structured query language

Question

What is the purpose of joins if we can collect data from multiple tables through

SELECT ,table1.a  , table2.b  , FROM table1,table2 ...

Answer 1

The syntax you've shown is in fact a join. It's called an implicit join. The join syntax is called an explicit join, and has the same effect, with a few advantages:

1. It's the standard, ANSI, way of doing things
2. As the name says, it's explicit - it's easier to understand where the join is, and to separate the join conditions (in the on clause) from the logical conditions.
3. It's easier to specify different types of joins (inner/outer/cross), which some databases may not allow in the implicit form, at least not with a standard syntax.

Answer 2

A JOIN allows you to return all or selected data from multiple tables into a single temporary table. Using single SELECT commands per table would leave you with multiple datasets rather than a single source.

Answer 3

Joins are useful for bringing data together from different tables based on their database relations.

Try to go on this link to learn much about this topic http://www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL

Answer 4

Comma is from before explicit JOIN syntax. It is a cross join of two tables: all possible combinations of a row from each. WHERE keeps only rows that meet its conditon. Given comma and WHERE and nested SELECTS, the alternative spelling CROSS JOIN and JOIN on another condition in ON is not needed.

However the OUTER JOINs ON a condition result in the rows from comma plus a WHERE-like restiriction to rows satisfying the condition plus the unmatched rows from either the left table (LEFT JOIN), the right table (RIGHT JOIN) or both (FULL JOIN) extended with a row of NULLs for the columns from the other table. This requires specifying a match condition (determining unmatched rows where NULLs are added to the cross join that comma would give) separately from later restriction via WHERE.

But as long as an ON condition is added for OUTER JOINs one might as well allow it for inner joins. It gives the same result as an outer join ON that condition returns less any unmatched rows. That is the same as doing a cross join then (like WHERE) restricting per the condition. So (INNER) JOIN is a comma with its own condition from ON, and CROSS JOIN is comma with no ON. (CROSS JOIN is like (INNER) JOIN ON 1=1.) (Also comma has lower precedence then the explicit non-comma joins.)

TL;DR Comma is low-precedence JOIN; we don't need explicit INNER JOIN or ON; OUTER JOINs need ON distinct from WHERE; we might as well then add explicit inner JOIN ON syntax.