In the previous post (Databases Introduction) we looked at different types of databases and simple concepts of tables. In this section, we will look at some basics of databases- tables, inner joins and outer joins. The syntax of SQL used here is for Oracle. However, it is very similar in all other databases such as MySQL, MS SQLServer, Postgres, etc..
Tables
We will create two tables by using the SQL statements below:
In the second SQL we create the table invoice, which has its columns and also the cust_id column from the customer table. This is how we delegate that a specific invoice is associated to a specific customer.
Observe that one customer can have many invoices and hence we have a one-many relationship between the customer and invoice tables. This implies that a customer may not have any invoices i.e. a one-zero relationship between the customer and invoice tables.
We can make the above create SQLs complex by specifying the tablespace name (we are using the default tablespace assigned to the user you are logging on) and adding foreign key constraints to the cust_id in the invoice table to the customer table. This will eliminate the input of data into the invoice table for which a cust_id is non-existent in the customer table.
A simple way to input data into the tables is to use the insert SQL statement which is given below:
insert into customer values (100, 'Paradigm Solutions');
insert into customer values (102, 'Siemens Pvt Ltd');
insert into customer values (104, 'British Telecom');
commit;
Now when we do a simple select from the customer table we get:
CUST_ID CUSTOMER_NAME
---------------------- ------------------------------------------------------------
100 Paradigm Solutions
102 Siemens Pvt Ltd
104 British Telecom
Now here are the SQL statements for inserting records into the invoice table.
insert into invoice values ( 8442, 102, 5430.00, 'Y');
insert into invoice values ( 8997, 102, 5430.00, 'Y');
insert into invoice values ( 8775, 102, 5430.00, 'N');
insert into invoice values (9876,100,2345.78,'N') ;
insert into invoice values (9886,100,345.00,'N');
commit;
As an exercise, we leave to the reader to retrieve the records inserted into the table invoice.
Inner and Outer Joins
To retrieve customers and invoices for which there are invoices, we do an inner join where the SQL is constructed by joining the column cust_id from the table customer and invoice:
select c.cust_id, customer_name, i.invoice_id, i.invoice_total, i.paid_ind
from customer c, invoice i where c.cust_id= i.cust_id;;
gives results as:
CUST_ID CUSTOMER_NAME INVOICE_ID INVOICE_TOTAL PAID_IND
---------------- -------------------------------------------------- ---------------------- ---------------------- --------
100 Paradigm Solutions 9876 2345.78 N
100 Paradigm Solutions 9886 345 N
102 Siemens Pvt Ltd 8775 5430 N
102 Siemens Pvt Ltd 8997 5430 Y
102 Siemens Pvt Ltd 8442 5430 Y
We can make our SQL criteria (the sql after the where keyword) complex by requesting only those which have not been paid by modifying as below:
where c.cust_id= i.cust_id and i.paid_ind ='N'
This will return those invoices which have not paid for the customers who have invoices.
Observe that we have used aliases for table names customer, invoiced as c and i respectively. This eliminates any ambiguous column names like cust_id being specified in the SQL. This, as a result, also eliminates typing the complete table name when referring the columns in the criteria or in the select.
The above was an example of inner join where we get the records when they exist in both tables. However, if you remember that we do not get the third customer - British Telecom from the table customer as it has no invoices associated with it. In order to obtain all the customer names and their invoices, whether they exist or not, we will write an outer join as shown:
select c.cust_id, customer_name, i.invoice_id from customer c, invoice i where c.cust_id= i.cust_id (+);
returns the results as shown below:
CUST_ID CUSTOMER_NAME INVOICE_ID
---------------------- ---------------------------------------------------- ----------------------
100 Paradigm Solutions 9876
100 Paradigm Solutions 9886
102 Siemens Pvt Ltd 8775
102 Siemens Pvt Ltd 8997
102 Siemens Pvt Ltd 8442
104 British Telecom
Observe that in the outer join SQL we specify in the join (+) which is equivalent to writing as:
select c.cust_id, customer_name, i.invoice_id from customer c left outer join invoice i on i.cust_id= c.cust_id ;
The latter method is more readable and specifies whether it is left or right outer join while the (+) indicates how the tables are already joined and the right , left outer is dependent on which side of the = sign the (+) is assigned.
Nowadays in Object Oriented Programming (OOP), we can understand both of these joins specifically when using Object Relation Mapping (ORM) tools like Hibernate, topLink etc. Most of the time we are using an outer join to get the most relevant information and hence in Hibernate (of which we will see in future articles) by default it uses outer join.
We will create two tables by using the SQL statements below:
- create table customer (cust_id number(4,0) not null, customer_name varchar2(60) not null);
- create table invoice (invoice_id number(4) not null, cust_id number(4) not null , invoice_total number(10,2) default 0.00, paid_ind char(1));
In the second SQL we create the table invoice, which has its columns and also the cust_id column from the customer table. This is how we delegate that a specific invoice is associated to a specific customer.
Observe that one customer can have many invoices and hence we have a one-many relationship between the customer and invoice tables. This implies that a customer may not have any invoices i.e. a one-zero relationship between the customer and invoice tables.
We can make the above create SQLs complex by specifying the tablespace name (we are using the default tablespace assigned to the user you are logging on) and adding foreign key constraints to the cust_id in the invoice table to the customer table. This will eliminate the input of data into the invoice table for which a cust_id is non-existent in the customer table.
A simple way to input data into the tables is to use the insert SQL statement which is given below:
insert into customer values (100, 'Paradigm Solutions');
insert into customer values (102, 'Siemens Pvt Ltd');
insert into customer values (104, 'British Telecom');
commit;
Now when we do a simple select from the customer table we get:
CUST_ID CUSTOMER_NAME
---------------------- ------------------------------------------------------------
100 Paradigm Solutions
102 Siemens Pvt Ltd
104 British Telecom
Now here are the SQL statements for inserting records into the invoice table.
insert into invoice values ( 8442, 102, 5430.00, 'Y');
insert into invoice values ( 8997, 102, 5430.00, 'Y');
insert into invoice values ( 8775, 102, 5430.00, 'N');
insert into invoice values (9876,100,2345.78,'N') ;
insert into invoice values (9886,100,345.00,'N');
commit;
As an exercise, we leave to the reader to retrieve the records inserted into the table invoice.
Inner and Outer Joins
To retrieve customers and invoices for which there are invoices, we do an inner join where the SQL is constructed by joining the column cust_id from the table customer and invoice:
select c.cust_id, customer_name, i.invoice_id, i.invoice_total, i.paid_ind
from customer c, invoice i where c.cust_id= i.cust_id;;
gives results as:
CUST_ID CUSTOMER_NAME INVOICE_ID INVOICE_TOTAL PAID_IND
---------------- -------------------------------------------------- ---------------------- ---------------------- --------
100 Paradigm Solutions 9876 2345.78 N
100 Paradigm Solutions 9886 345 N
102 Siemens Pvt Ltd 8775 5430 N
102 Siemens Pvt Ltd 8997 5430 Y
102 Siemens Pvt Ltd 8442 5430 Y
We can make our SQL criteria (the sql after the where keyword) complex by requesting only those which have not been paid by modifying as below:
where c.cust_id= i.cust_id and i.paid_ind ='N'
This will return those invoices which have not paid for the customers who have invoices.
Observe that we have used aliases for table names customer, invoiced as c and i respectively. This eliminates any ambiguous column names like cust_id being specified in the SQL. This, as a result, also eliminates typing the complete table name when referring the columns in the criteria or in the select.
The above was an example of inner join where we get the records when they exist in both tables. However, if you remember that we do not get the third customer - British Telecom from the table customer as it has no invoices associated with it. In order to obtain all the customer names and their invoices, whether they exist or not, we will write an outer join as shown:
select c.cust_id, customer_name, i.invoice_id from customer c, invoice i where c.cust_id= i.cust_id (+);
returns the results as shown below:
CUST_ID CUSTOMER_NAME INVOICE_ID
---------------------- ---------------------------------------------------- ----------------------
100 Paradigm Solutions 9876
100 Paradigm Solutions 9886
102 Siemens Pvt Ltd 8775
102 Siemens Pvt Ltd 8997
102 Siemens Pvt Ltd 8442
104 British Telecom
Observe that in the outer join SQL we specify in the join (+) which is equivalent to writing as:
select c.cust_id, customer_name, i.invoice_id from customer c left outer join invoice i on i.cust_id= c.cust_id ;
The latter method is more readable and specifies whether it is left or right outer join while the (+) indicates how the tables are already joined and the right , left outer is dependent on which side of the = sign the (+) is assigned.
Nowadays in Object Oriented Programming (OOP), we can understand both of these joins specifically when using Object Relation Mapping (ORM) tools like Hibernate, topLink etc. Most of the time we are using an outer join to get the most relevant information and hence in Hibernate (of which we will see in future articles) by default it uses outer join.
Comments