关联关系

不等值关系

select a.ename,a.sal,b.grade    from emp a, salgrade b

where a.sal between b.losal and b.hisal

 

等值连接：平时用最多

 

外连接：outer-join

select a.ename, a.sal, a.job, b.deptno, b.ename

from emp a right join dept b on a.deptno = b.deptno

order by deptno

 

 

左外连接    右外连接    全外连接

 

内连接：

loan inner join borrower    on loan.loan_number  borrower.loan_number

as lb(loan_number, branch, amount, cust, cust_loan_num)

 

左外连接：

loan left join borrower    on loan.loan_number  borrower.loan_number

 

找出所有在银行有存款而无贷款的客户

select d_CN

from( depositor left outer join borrower

        on depositor.customer_name = borrower.customer_name)

    as db1(d_CN, account_number, b_CN, loan_number)

where b_CN is null