Oracle has made some important changes in Oracle8i SQL. These changes include new execution logic, scalar subquery support, SQL ISO 99 support. As a consequence, the syntax of SQL statements also changes in the direction Easier to use when working with databases. Here are some typical changes in the syntax of SQL statements: Oracle has made some important changes in Oracle8i SQL. These changes include new execution logic, scalar subquery support, SQL ISO 99 support. As a consequence, the syntax of SQL statements also changes in the direction Easier to use when working with databases. Here are some typical changes in the syntax of SQL statements: - CROSS JOIN - Create a set of threads (sorted pairs) of rows in two tables, like Oracle 8i when connecting the table but ignoring the WHERE clause. - NATURAL JOIN - This is a very handy syntax in Oralce 9i, increasing the readability of SQL statements by eliminating the connection criteria in the WHERE clause. - USING clause - Allows you to define a connection key by name. - The ON clause - Defines the columns for the connection keys in both tables. - LEFT OUTER JOIN - Returns all rows in the table to the left of the connection with the corresponding values in the right table or null if there are no corresponding rows in the table to the right. - RIGHT OUTER JOIN - Returns all rows in the table to the right of the connection with the corresponding values in the left or null table if there are no corresponding rows in the table to the right. - FULL OUTER JOIN - Returns all lines from two tables, replacing any null values with null. There are no equivalent clauses in Oracle8i. Most of these changes are intended to allow non-Oracle applications to be more easily compatible with the Oracele database. Note that these are only differences in syntax, the ISO 99 standard does not provide any new features for Oracle9i SQL. CROSS JOIN In Oracle, the CROSS JOIN clause generates a Cartesian product area in two tables, just as if we ignored the WHERE clause when connecting as follows: select last_name, dept_id from emp, depts; In Oracle9i, we can use the CROSS JOIN clause to get the same result as the statement above: select last_name, dept_id from emp CROSS JOIN dept; NATURAL JOIN The NATURAL JOIN syntax is very useful because it automatically recognizes the connection keys from the same column names in both tables. This syntax simplifies Oracle9i SQL but requires that the connection columns in the two tables have the same name. One particular thing is that this syntax works even if there is no key / foreign key relationship in the connection table of the two tables. Oracle8i Chọn book_title, sum (quantity) From book, sales Where book.book_id = sales.book_id nhóm bởi book_title; Oracle9i Chọn book_title, sum (quantity) từ sách natural join sales nhóm bởi book_title; The USING clause Use the USING clause if there are more than one columns with the same name in the two tables but you do not want to connect on all of these columns. Oracle8i select dept_id, city from departments, locations where departments.location_id = location.location_id; Oracle9i select department_name, city from departments JOIN locations USING (location_id); The ON clause The ON clause is used to connect two tables when the names of the columns in the two tables do not match. Oracle8i select department_name, city từ vị trí, vị trí where department.location_id = location.loc_id; Oracle9i select department_name, city from department d JOIN location l ON (d.location_id = l.id); Mutable Join Use when you need to connect multiple tables together. The ISO 99 standard by default consistently links between left and right, and the current connection condition is only referenced to the current connection and the previous connections to the left. Oracle8i select emp_id, city_name, dept_name from location l, department d, emp e where d.location_id = l.location_id and d.department_id = e.department_id; Oracle9i select emp_id, city_name, dept_name from locations l JOIN departments d ON (d.location_id = l.location_id) JOIN employees e ON (d.department_id = e.department_id); New OUTER JOIN syntax The ISO 99 standard removes all of the plus (+) signatures from the extended connection and hence the extended connections become easier to understand. LEFT OUTER JOIN In the LEFT OUTER JOIN, all the rows in the left table are returned, even if there is no corresponding value in the column of the right table. In the example below, all employee names are returned, even unassigned employees. Oracle8i select last_name, dept_id from emp e, dept d where e.department_id = d.department_id (+); Oracle9i select last_name, dept_id from emp LEFT OUTER JOIN Dept ON e.dept_id = d.dept_id; RIGHT OUTER JOIN In RIGHT OUTER JOIN, all rows in the left table are returned, even if there is no corresponding value in the column of the right table. In the example below, all room names are returned, even those without staff. Oracle8i select lastname, d.dept_id từ nhân viên e, departments d where e.department_id (+) = d.department_id; Oracle9i select lastname, d.dept_id from employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); Nguyen Huu Tung (Theo builder.com)
