SQL-92 and SQL-89 joins are supported by most RDBMSs, including Apache Impala. This article will explain how the syntax is written in both ways and which is the better of the two.
A JOIN clause is used to join rows from two or more tables together based on a related column. The tables that need to be joined together will be specified in the FROM clause in SQL-89, along with a WHERE clause to indicate the fields that need to be examined for matched or unmatched entries. An example is given below.
To retrieve matched records from the both the tables, use the below statement.
SELECT * FROM emp, dept WHERE emp.deptno=dept.deptno;
It is equivalent to the following SQL-92 styled join statement.
SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno;
To retrieve all the records from “emp” table and only matched records from “dept” table, then use the outer join operator as shown in the below statement.
SELECT * FROM emp, dept WHERE emp.deptno=dept.deptno(+);
It is equivalent to the following SQL-92 styled join statement.
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;
To retrieve matched records from “emp” table and all the records from “dept” table, then use the outer join operator as shown in the below statement.
SELECT * FROM emp, dept WHERE emp.deptno(+)=dept.deptno;
It is equivalent to the following SQL-92 styled join statement.
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
If you miss the WHERE clause while writing the JOIN statements in SQL-89 style, then you’ll get “Cross Join” results based on cartesian product.
Hope you find this article helpful.
Please do follow us for more interesting updates.
Big Data and SQL 
3 comments