SQL Continued VI

Consider the following table schemas and then go over SQL queries and their corresponding expressions in response to the queries that follow:

Customer:

customer_id | customer_name | customer_street | customer_city

Branch

branch_name | branch_city | assets

Account:

account_number | type | balance | branch_name

                                                             fk

Deposit:

customer_id | account_number

Loan:

loan_number | branch_name | amount

                                 fk

Borrow:

customer_id | loan_number

Payment:

pay_no | loan_number | date | amount

                       fk

Employee:

employee_id | name | designation | contact_no

Serves:

customer_id | employee_id | date

Let us consider the following relations on loan and borrow from the banking example:

Table 1: Loan relation

loan_number	branch_name	Amount
L-170	Kakrail	3000
L-230	Motijheel	4000
L-260	Dhanmondi	1700

                                 

Table 2: Borrow relation

customer_name	loan_number
Kamal	L-170
Tinku	L-230
Jones	L-155

We start with a simple example of inner joins:

loan inner join borrow on loan.loan_number = borrow.loan_number

The expression computes the normal join of loan and borrow relations with the join condition being loan.loan_number = borrow.loan_number

The result of the above join is shown below:

Table 3: The result of loan inner join on loan.loan_number = borrow.loan_number

 

loan_number	branch_name	amount	customer_name	loan_number
L-170	Kakrail	3000	Kamal	L-170
L-230	Motijheel	4000	Tinku	L-230

We may rename the result relation of a join and the attributes of the result relation by using an ‘as’ clause as shown below:

loan inner join borrow on loan.loan_number = borrow.loan_number as lb(loan_number, branch, amount, cust, cust_loan_num)

We rename the second occurrence of loan_number to cust_loan_num. The ordering of the attributes in the result of the join is important for the renaming.

Now we consider an example of left outer-join operation:

Loan left outer join borrow on loan.loan_number = borrow.loan_number

The following table shows the result of the above left outer join.

Table 4: The result of loan left outer join borrow on loan.loan_number = borrow.loan_number

loan_number	branch_name	amount	customer_name	loan_number
L-170	Kakrail	3000	Kamal	L-170
L-230	Motijheel	4000	Tinku	L-230
L-260	Dhanmondi	1700	NULL	NULL

In the resultant relation, the tuples (L-170, Kakrail, 3000) and (L-230, Motijheel, 4000) from loan join with tuples from borrow and appear in the result of the inner join and hence in the result of the left outer join. On the other hand the tuple(L-260, Dhanmondi, 1700, NULL, NULL) is present in the result of the left outer join.

Finally we consider an example of natural-join operation:

loan natural inner join borrow

This expression computes the natural join of the two relations. The only attribute name common to loan and borrow is loan_number and it appears once in the result unlike the result of the join with the ‘on’ condition.

The right outer join is symmetric to the left outer join. Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls and are added to the result of the right outer join.

Here is an example of combining the natural-join condition with the right outer join type:

loan natural right outer join borrow

The result of the above erxpression is shown below:

Table 5: The result of loan natural right outer join borrow 

loan_number	branch_name	amount	customer_name
L-170	Kakrail	3000	Kamal
L-230	Motijheel	4000	Tinku
L-155	NULL	NULL	Jones

Now we consider a full outer join operation. For example the following table shows the result of full outer join expression.

loan full outer join borrow using (loan_number)

Table 6: The result of loan full outer join borrow using (loan_number)

loan_number	branch_name	amount	customer_name
L-170	Kakrail	3000	Kamal
L-230	Motijheel	4000	Tinku
L-260	Dhanmodi	1700	NULL
L-155	NULL	NULL	Jones

As another example, we can write the query, “Find all customers who have either an account or loan but not both at the bank” with natural full outer join as:

SELECT customer_name FROM (deposit natural full outer join borrow)

WHERE account_number is NULL OR loan_number is NULL