Skip to content

multiTableAndJoin.md

Latest commit

 

History

History
119 lines (110 loc) · 4.96 KB

multiTableAndJoin.md

File metadata and controls

119 lines (110 loc) · 4.96 KB
  • select * from dual trả về single row
  • sub query trong câu select phải là srf

JOIN

inner join - simple join:

  • lấy phần tử chung của 2 tập (intersect).
  • Oracle INNER JOINS return all rows from multiple tables where the join condition is met.
select * from emp [inner] join dept on emp.no = dept.no
  • old syntax:
select * from emp,dept where emp.no = dept.no

LEFT [OUTER] JOIN - LEFT JOIN:

  • lấy full bên trái.
  • This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
  • old syntax:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT [OUTER] JOIN - RIGHT JOIN:

  • lấy full bên phải.
  • This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
  • old syntax
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id(+) = orders.supplier_id;

FULL [OUTER] JOIN - FULL JOIN:

  • This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met

SELF JOIN:

  • join với chính nó

cross join - cartesian product - cross product - no join:

  • ghép 2 bảng lại với nhau, ko quan tâm điều kiện (14 * 4 = 56 bản ghi):
select * from emp,dept
select * from emp cross join dept

natural join:

  • inner join nhưng không chỉ định cột cần join (có thể nhiều cột chứ ko phải chỉ 1 cột, 1 cột là outdate)
select * from emp natural join dept
  • join nhiều cột có thể xảy ra kết quả ko mong muốn

using (in join):

  • tương tự natural join nếu chỉ có 1 cột làm tham số
select * from emp [inner|left|right] join dept using (sal1,sal2)
  • USING implicitly performs an equality comparison (=), while ON allows for various comparison operators (e.g., <, >, !=).

Note

  • inner join = equijoin
  • equijoin: 2 cột giống nhau dùng để join (equal)
  • non-equijoin: không có 2 cột nào có thể dùng để join (not equal, ví dụ: between)

EXISTS

  • The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row
  • The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.
  • Oracle SQL statements that use the Oracle EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.
  • exist: chỉ cần có record trả về, null cũng được
  • outer stop tính toán inner khi first value found
  • để check outer có tồn tại trong inner ko?

CASE

  • If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.

  • If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

  • You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.

  • Ví dụ

    • You could use the CASE statement in a SQL statement as follows: (includes the expression clause)
    SELECT table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
FROM all_tables;
    • Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)
    SELECT table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
FROM all_tables;
    • Here is an example that demonstrates how to use the CASE statement to compare different conditions:
    SELECT
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
FROM suppliers;
    • below is an example of a case statement that evaluates two different fields
    SELECT supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;

Other

  • The Oracle join syntax does not support natural joins. Oracle's traditional (non-ANSI) join syntax does not support NATURAL JOIN. That is a feature of ANSI SQL