create table loan(
  loan_number varchar(9) primary key,
  branch_name varchar(11),
  amount integer);

#   foreign key (loan_number) references loan(loan_number)  
create table borrower(
   customer_name varchar(10) primary key,
   loan_number varchar(9)
);  
insert into loan(loan_number,branch_name,amount) values
            ('L-170','Downtown',3000),
            ('L-230', 'Redwood',4000),
            ('L-260','Perryridge', 1700);    
 
insert into borrower(customer_name,loan_number) values
            ('Jones', 'L-170'),
            ('Smith', 'L-230'),
            ('Hayes', 'L-155');

select *
from loan, borrower
where loan.loan_number=borrower.loan_number;

select *
from loan inner join borrower on loan.loan_number=borrower.loan_number;

select * 
from loan left outer join borrower on loan.loan_number = borrower.loan_number;

select *
from loan natural inner join borrower;

select *
from loan natural right outer join borrower;

select *
from loan full outer join borrower on loan_number;

#mysql does not support full outer join instead;

select * 
from loan left outer join borrower on loan.loan_number = borrower.loan_number
union all
select * 
from loan right outer join borrower on loan.loan_number = borrower.loan_number;



