The select statement is the most common and well-known SQL command;it 's used to extract data from one or more tables (or views)of a database. In its simplest form,the command is

        select <fields> from <table>

select upper(name), (lastname || "," || firstname) as fullname from customers

select * from customers where address is not null and  cust_no between 100 and 150

select * from employee  where upper(last_name) like "B%N"

select Distinct city from customer

select max(salary) as maxsal, ave(salary), department from employee

group by department

having max(salary) > 40000

select * from Employee

where salary = (select max(salary) from Employee)

Inner and Outer Joins

Up to now,our example select statements have worked on single tables —a serious limita-

tion for a relational database.The operation of merging data from multiple source tables is

called a table join .The SQL standard supports two types of joins,called inner and outer .

An inner join can be written directly in the where clause:

select *

from <table1>,<table2>

where <table1.keyfield>=<table2.externalkey>

This is a typical example of an inner join used to extract all the fields of each table involved.     An inner join is handy for tables with a one-to-one relation (one record of a table corresponding only to one record of the second table).

An outer join,instead,can be specifically requested with the statement:

select *

from <table1>left outer join <table2>

on <table1.keyfield>=<table2.keyfield>

The main difference from an inner join is that the selected rows of an outer join will not consider the null fields of the second table.

ex.:

 statement below show's a good select with Join statement for two(2) tables with valid restrictions on the type of account to be fetch by the SQL engine.

Note: 

for type DBTitle.cType value there are only two(2); 'IN' and 'OUT' for this example we needed to sum up all the expenses(the OUT type):

select cTitle, sum(Amount) as Totals

from DB_Exp

left outer join DBTitle on

DB_Exp.cTitle = DBTitle.CTitle

group by cTitle

having (cCode = 'BRT' and DBTitle.cType = 'OUT')

and (rDate between '9/9/1999' and '9/9/2003')

Order by cTitle

a more accurate way is move the conditions from the having clauses to the standard where clauses, that way there won't be any blank or skip'd records

select cTitle, sum(Amount) as Totals

from DB_EXP

left outer join DBTitle on 

DB_EXP.cTitle = DBTitle.cTitle

where (cCOde = 'BRT' and DBTitle.cType = 'OUT')

and (rDate between '9/9/1999' and '9/9/2003')

group by cTitle

order by cTitle

select cTitle, count(*), sum(amount)

from DB_EXP

where (cCode = 'BRT' and cTitle = 'GAS & OIL')

and (rDate between '9/9/2000' and '12/31/2003')

group by cTitle