I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.

Assume these two tables:

SELECT * FROM faculty;

ID FIRST_NAME LAST_NAME
1 Jon Emmons
2 Zach Tirrell
3 Evelyn Stiller

SELECT * FROM class;

CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 CS3600 1
2 CS3020 2
3 CS2000  
4 CS1100 1

Inner Join

The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.

SELECT *
FROM faculty, class
WHERE faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
1 Jon Emmons 4 CS1100 1

Here we see the three rows where faculty.id had a match to class.instructor_id.

Left Join

A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.

SELECT *
FROM faculty LEFT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      

Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.

Right Join

Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.

SELECT *
FROM faculty RIGHT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
      3 CS2000  
1 Jon Emmons 4 CS1100 1

Now we see rows from the class table which do not have a faculty counterpart.

Outer Join

The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.

SELECT *
FROM faculty FULL OUTER JOIN class
ON faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      
      3 CS2000  

Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.

oracle, sql, dba, database administration, database development

A common question amongst database developers is “How do I get dates and time into and out of the database in the format my script/program/table expects?” This information is based on Oracle, however I expect much of this will apply to other databases.

About the DATE and TIMESTAMP datatypes: The DATE datatype is 7-bytes, comprised of date and time information to the precision of 1 second.
TIMESTAMP can be from 7 to 11 bytes depending on the precision specified. Timestamps can represent date and time as small as the nanosecond (.000000001 seconds) The default is to a microsecond of precision (.000001 seconds.)

Note: dual is a special table for testing and development. It’s useful for returning values (results from functions or contents of variables) not stored in tables, for instance the current date.

Let’s start with some information we can grab from the system about the current date and time.

SELECT sysdate FROM dual;

SYSDATE
---------
14-SEP-05


This simple select statement returns the date in the standard format (typically DD-MON-YY.)

SELECT systimestamp FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-SEP-05 04.06.31.264201 PM -04:00

Here we see that systimestamp reports more detail than sysdate, including the offset from GMT.

Displaying Dates

The to_char function will allow you to describe how you want dates displayed and will convert them to a character string in that format. The default in Oracle is DD-MON-YY. The default format can be changed by setting the nls_date_format parameter.

SELECT to_char(sysdate, 'MM/DD/YYYY') FROM dual;

TO_CHAR(SY
----------
09/14/2005

As seen here, the to_char function requires two parameters: a date to display, and the format you want it to be in. There are dozens of formatting options, but here are some common ones:

SELECT to_char(sysdate, 'MM/DD/YY') FROM dual;

09/14/05

SELECT to_char(sysdate, 'MM/DD/YYYY HH:MI:SS') FROM dual;

09/14/2005 04:09:03

SELECT to_char(sysdate, 'DAY, MONTH DD, HH12:MI AM') FROM dual;

WEDNESDAY, SEPTEMBER 14, 04:09 PM

SELECT to_char(sysdate, 'YYYY BC') FROM dual;

2005 AD

SELECT to_char(systimestamp, 'HH24:MI:SS.FF3') FROM dual;

16:09:24.606

There are several more options than are displayed here. Your databases documentation should have a full list. These components can be used in any order or combination. These characters, as well as spaces can be used to format dates / . – : . ;

Inserting Dates

to_date works similarly to the to_char function above. You must specify a date, typically enclosed by single quotes, then describe the format with the date components as above. To demonstrate this we’ll create a table we can insert some dates into.

CREATE TABLE dates
(
entry NUMBER,
entry_date DATE,
CONSTRAINT pk_dates PRIMARY KEY (entry)
);

Now a few inserts:

INSERT INTO dates (entry, entry_date)
VALUES (1, sysdate);

Inserts the current date and time to the second.

INSERT INTO dates (entry, entry_date)
VALUES (2, to_date('09/27/05', 'MM/DD/YY'));

INSERT INTO dates (entry, entry_date)
VALUES (3, to_date('10/02/2005 10:05:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO dates (entry, entry_date)
VALUES (4, to_date('17:01:24', 'HH24:MI:SS'));

INSERT INTO dates (entry, entry_date)
VALUES (5, to_date('Monday, September 12, 2:30 PM', 'DAY, MONTH DD, HH:MI AM'));

COMMIT;

Now let’s take a look at the data in the dates table:

SELECT entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
FROM dates;

1 09/14/2005 09:08:32 PM
2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM
4 09/01/2005 05:01:24 PM
5 09/12/2005 02:30:00 PM

We can see that the current date and time was entered in entry 1 down to the second.

Entry 2 contains the date we entered, but since we did not specify the time it has defaulted to midnight.

Entry 3 shows a complete timestamp exactly as we specified.

In entry 4 we see the time as we specified, but since we didn’t specify a date it has defaulted to the first of this month. I have a feeling this varies from database to database. Probably best not to rely on this.

Entry 5 shows the date and time, however since we did not specify seconds they display as :00.

Comparing Dates

Dates can be compared much like other values. To demonstrate this we’ll do some quick selects on the table we just created.

select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
from dates
where entry_date > to_date('09/20/2005', 'MM/DD/YYYY');

2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM

select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
from dates
where entry_date < sysdate; 1 09/14/2005 09:08:32 PM 4 09/01/2005 05:01:24 PM 5 09/12/2005 02:30:00 PM select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM') from dates order by entry_date; 4 09/01/2005 05:01:24 PM 5 09/12/2005 02:30:00 PM 1 09/14/2005 09:08:32 PM 2 09/27/2005 12:00:00 AM 3 10/02/2005 10:05:33 PM

Those are the highlights. Most things you'll need to do will be some type of variation on these.

oracle, sql, dba, database administration, database development

The blog entry I was going to write here would have covered the format and most of the major options for the SELECT statement. While doing a little checking on the internet before posting I realized I am a complete idiot! W3Schools.com, one of the best resources on the web for programming tutorials has a fantastic tutorial on SQL.

It covers the basics of getting data into and out of your database. Beyond the tutorial information, the site includes an interactive demonstration which lets you try out your newly found SQL knowledge. There’s even this SQL Quick Reference to help you remember what you learned.

So check out this tutorial on SQL and don’t miss out on W3Schools.com other fine tutorials.

sql, oracle, dba, database, database administration

Here is a simple example of the SQL used to create a couple tables, populate them, create a view, and grant access to view them.

Create the two tables:

create table name
(
id number not null,
first_name varchar2(30),
last_name varchar2(30) not null,
constraint pk_name primary key (id)
);

create table office_location
(
name_id number not null,
room_number varchar2(6 byte),
building varchar2(20 byte),
constraint person_exists foreign key(name_id)
references name,
constraint room_number_check check (room_number between 0 and 799)
);

Insert some values into each:

insert into name (id, first_name, last_name)
values (1, 'Jon', 'Emmons');

insert into name (id, first_name, last_name)
values (2, 'Matt', 'Batchelder');

insert into name (id, first_name, last_name)
values (3, 'Zach', 'Tirrell');

insert into name (id, first_name, last_name)
values (4, 'Jon', 'Graton');

insert into office_location (name_id, room_number, building)
values (1, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (2, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (3, '222', 'Hyde');

insert into office_location (name_id, room_number, building)
values (4, '333', 'Hyde');

Extract some meaningful data with a simple join:

select first_name, last_name, building, room_number
from name, office_location
where id=name_id;

Create a view based on that join:

create view name_office as
(
select first_name, last_name, building, room_number
from name, office_location
where id=name_id
);

Select from our new view:

select * from name_office;

Grant access to jemmons to the tables and view (note that you do not have to grant access to the underlying tables for a user to be able to select from the view):

grant select on name to jemmons;

grant select on office_location to jemmons;

grant select on name_office to jemmons;

Technorati tags: , , ,

One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”

If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:

DBA_SYS_PRIVS – Probably most important, this show system privileges granted to users and roles

DBA_TAB_PRIVS – While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions

DBA_COL_PRIVS – Not always used, but this view tracks grants on columns

DBA_ROLE_PRIVS – This shows who (or what roles) a role has been granted to

These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.

Stay tuned for some handy queries to run on these to get quick reports on who can see what.

Technorati tags: , , , , , ,

« Previous PageNext Page »