[Oracle SQL]Kataxian結合(Cartesian)

4699 ワード
SQL Catasian結合 テキストリンク
2)Cartesian JOIN
  • は、すべての可能な行の組合せを結果として返します.
  • 条件または定義エラーがない場合、
  • 必要がない限り、
    • を使用しないでください.
    JOIN 、例えば
  • ANSIにおけるCROSS JOIN
    • Aテーブル(100行20列)とBテーブル(30行10列)をデカルトに結合すると、結果は(3000行30列)
    使用例)
    SELECT 'CART' AS 테이블명, COUNT(*) AS 자료수
         FROM CART
        UNION

 SELECT 'CART', COUNT(*)
           FROM CART
          UNION
         SELECT 'BUYPROD', COUNT(*) 
           FROM BUYPROD
          UNION  
         SELECT 'PROD', COUNT(*) 
           FROM PROD;
         
         SELECT COUNT(*) FROM BUYPROD,CART,PROD;
         
         (ANSI-CROSS JOIN)
          SELECT COUNT(*)
            FROM BUYPROD
            CROSS JOIN CART
            CROSS JOIN PROD;
    3) SELF JOIN
    1つのテーブルに2つの別名を追加することで、独自のテーブル間に結合を作成します.
    使用例)従業員表の従業員番号120回/従業員給与が従業員給与より高い従業員の
    従業員番号、従業員名、部門番号、部門名、給与を照会してください
          SELECT C.EMPLOYEE_ID AS 사원번호, 
             C.EMP_NAME AS 사원명, 
             C.DEPARTMENT_ID AS 부서번호, --C도되고 B도됨 
             B.DEPARTMENT_NAME AS 부서명, 
             C.SALARY AS 급여
        FROM HR.EMP A, DEPT B, HR.EMP C
       WHERE A.EMPLOYEE_ID=120
         AND A.SALARY<=C.SALARY --조인조건
         AND C.DEPARTMENT_ID= B.DEPARTMENT_ID --조인조건
       ORDER BY 3, 5 DESC;
    4) Non-Equi Join
    「
  • 」結合条件セクションで非等しい演算子(=)を使用する場合は、
    • を使用します.
    演算子
  • 、例えば不等式、IN、ANYを用いる場合、
    • となる.
    使用例)部門マネージャの入社日が30日より早い従業員情報ルールを従業員表に問い合せてください
    Aliasは、従業員番号、従業員名、職階名、入社日です.
    --非識別関係(点線):
    --識別子関係(直線):親テーブルが存在しない場合、子テーブルにexは存在しません.支払テーブルと受注テーブルの支払がキャンセルされた場合、受注も存在しません.
         SELECT C.EMPLOYEE_ID AS 사원번호, 
            C.EMP_NAME AS 사원명, 
            B.JOB_TITLE AS 직책명, 
            C.HIRE_DATE AS 입사일
       FROM HR.EMP A,-- 30번 부서의 관리자 한사람에 대한 정보
            HR.JOBS B,
            HR.EMP C, -- 사원
            HR.DEPT D
      WHERE D.DEPARTMENT_ID=30
        AND A.EMPLOYEE_ID=D.MANAGER_ID 
        -- EMP테이블에서 MANAGER_ID가 EMPLOYEE_ID의 부모
        AND C.HIRE_DATE<=A.HIRE_DATE 
        --해당하는 사람 여러명 있을텐데
        AND A.DEPARTMENT_ID=C.DEPARTMENT_ID 
        -- A.그 사람들중에서도  = C.30번 부서에 속한사람들
        AND C.JOB_ID=B.JOB_ID
      ORDER BY 4;
    使用例)40大会メンバー(サブクエリ)の平均マイル数よりも多くのマイルを持つ会員情報を会員表に問い合せてください
    Aliasは会員番号、会員名、年齢、ポイントを提供しております
      ```
     (서브쿼리: 40대회원의 평균마일리지)

       SELECT ROUND(AVG(MEM_MILEAGE))
         FROM MEMBER
        WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE) 
        - EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
              -- TRUNC(27,-1) = 20 첫째자리에서 모두 버리시오
       SELECT A.MEM_ID AS 회원번호, 
          A.MEM_NAME AS 회원명, 
          TRUNC(EXTRACT(YEAR FROM SYSDATE) 
          - EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대, 
          A.MEM_MILEAGE AS 마일리지
     FROM MEMBER A, (SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
                       FROM MEMBER
                      WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE) 
                      - EXTRACT(YEAR FROM MEM_BIR),-1) = 40)B
    WHERE B.AMILE <= A.MEM_MILEAGE; --조인조건

    
  
     (ANSI JOIN)
      SELECT ROUND(AVG(MEM_MILEAGE))
        FROM MEMBER
       WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
       - EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
             -- TRUNC(27,-1) = 20 
             첫째자리에서 모두 버리시오
       SELECT A.MEM_ID AS 회원번호, 
          A.MEM_NAME AS 회원명, 
          TRUNC(EXTRACT(YEAR FROM SYSDATE) 
          - EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대, 
          A.MEM_MILEAGE AS 마일리지
     FROM MEMBER A, (SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
                       FROM MEMBER
                      WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE) 
                      - EXTRACT(YEAR FROM MEM_BIR),-1) = 40)B
    WHERE B.AMILE <= A.MEM_MILEAGE; --조인조건
            (조인문을 쓰지 않은경우 - 속도 더느림)

       SELECT ROUND(AVG(MEM_MILEAGE))
         FROM MEMBER
        WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE) 
        - EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
       SELECT A.MEM_ID AS 회원번호, 
          A.MEM_NAME AS 회원명, 
          TRUNC(EXTRACT(YEAR FROM SYSDATE) 
          - EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대, 
          A.MEM_MILEAGE AS 마일리지
     FROM MEMBER A
    WHERE  A.MEM_MILEAGE>=(SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
                             FROM MEMBER
                            WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE) 
                      - EXTRACT(YEAR FROM MEM_BIR),-1) = 40);
    19. Webcam Fun
    11-3. 反転文字列