My tryst with Web Development

Tuesday, June 13, 2006

Oracle Joins

Definition / Concept

If we need to retrieve data from more than one table we use JOINs. A join is a query, used to retrieve data from more than one table based on JOIN condition (Note: Same table can referenced more than once).


In SQL terms, Join occurs when we specify more than one table in the FROM clause of the SELECT statement. Additionally, we also specify a condition in the WHERE clause to specify JOIN condition in the SELECT statement. Now, JOINs are conditions to join/compare two tables' data retrieved from the selected tables' columns based on JOIN Condition. Oracle combines the rows' data to compare with other tables' rows and evaulate the JOIN condition for TRUE/FALSE.

We need to qualify columns having same name with table names/aliases to avoid ambiguity errors, for joins can be extended to multiple tables. In case of multiple joins extending to more than 2 tables, let's say 3 for example, we create a recordset based on 1st join between 1st two tables & join the resulting recordset with 3rd table using 2nd Join.

Table 1 +
(JOIN)--->Recordset I +
| |
Table 2 + |
(JOIN)--- Final Recordset
Table 3
Now, we've understood what are joins, their need & application, let's see what are the different types of JOINs


All the above terms refer the same JOIN type. Let's call them INNER JOIN for our reference. Inner Join have equality operator as the JOIN condition. So, INNER JOIN returns only those rows from two tables that have same data for the specified columns.

+--------+              +---------+
| ID | | ID |
| AGE | | |
|--------| |---------|
+--------+ +---------+
We need to extract the salary of an employee from the payroll table. So, a INNER JOIN shall be created between EMPLOYEE & PAYROLL table, using ID columns from the two tables.

SELECT,, payroll.salary
FROM employee, payroll


If we compare the data of a table with itself, we create a self join. I'll give here a classical example of employees & managers. Suppose, we need to know the name of employees' manager from the employee table, we'll create a self join. For example purpose, I modify the EMPLOYEE table.
| ID |
| NAME |
| AGE |
| MGR_ID |
FROM employee e1, employee e2
WHERE e1.mgr_id =;


If there is no comparison condition specified in a JOIN condition, a cartesian product is returned. In such a case, every row in a table is joined with every row in the other table of the JOIN. Let's say, if there are 3 rows in Ist table & 4 rows in the IInd table, 12 rows (3x4) would be produced.
+--------+              +---------+
| ID | | ID |
| AGE | | |
|--------| |---------|
+--------+ +---------+
SELECT, payroll.salary
FROM employee, payroll


In a NATURAL JOIN all columns with same names & datatype are joined from both tables. Here, we need to qualify the column name by table name or alias. All rows matching with the same column data are retrieved.
+--------+              +---------+
| ID | | ID |
| AGE | | |
|--------| |---------|
+--------+ +---------+
SELECT id, name, salary
FROM employee NATURAL JOIN payroll;


Till now, all joins we've discussed retrieve all matching records from both tables. But sometimes, there is a situation when we also need to retrieve non-matching records from either one or both tables. For retrieving such non-matching records, we create OUTER JOINs. There are 3 type of OUTER JOINs based on from which side of the JOIN Condition we extract the non-matching records. We assume the following structure for our examples:
+--------+              +---------+
| ID | | |
| NAME | | ID |
| AGE | | SALARY |
| MGR_ID | | |
|--------| |---------|
+--------+ +---------+

As the name suggests, we extract non-matching records from the table mentioned on the right hand side of the JOIN condition. So, all the matching records from two tables, as well as non-matching records from the 2nd table (on right hand) are retrieved. NULL values are replaced for the non-existing values for columns extracted from table on the right hand side in non-matching rows.

SELECT, p.salary
FROM employee e, payroll p
WHERE = (+);


In this JOIN, we extract non-matching records from the table mentioned on the left hand side of the JOIN condition in addition to the matching records. NULL value is displayed for the columns extracted from table on the left hand side in the non-matching rows.

SELECT, p.salary
FROM employee e, payroll p
WHERE (+) =;


In case of a full outer join, we extract matching records from both tables specified in JOIN condition, as well as non-matching records from both tables. Actually to create FULL OUTER JOIN, a UNION set is generated from both LEFT OUTER JOIN & RIGHT OUTER JOIN. NULL values are displayed for non-existing records of the other table in each non-matching row of either table.

SELECT, p.salary
FROM employee e, payroll p
WHERE = (+)
SELECT, p.salary
FROM employee e, payroll p
WHERE (+) =;

  • SQL statements provided in the post were tested on Oracle 8 version only. Natural Joins don't exist on Oracle 8
  • ANSI syntax has not been covered here.

Monday, May 01, 2006

Intellectual Slavery

After around 4-5 months now, since I joined this company/project; I can confidently say that this place is inhibited by literate fools who at best can order Intellectual Slavery. Companies like this claim that they allow complete freedom to all employees in choosing/managing their career path. But, the reality is you are at your own risk/luck in managing your career, something not under your control most of the times.

Unless you are an experienced professional (though the definition of experienced is subject to discussion), as and when you join the company, the HR dept. tries to throw you in any damn project, they can think of. The Project manager takes you in, irrespective of the fact, whether you want to join the project or not, for they don’t need you to do work, but to increase the team head count, so as to claim higher amounts from the client. Just another brick in the wall, as my friend says.

Then you are asked about the level of satisfaction achieved in the first year of employment, and it is also expected that you’ll be proud to be a part of such organization, so idiotic are these fools.

Anyways, you try to start & continue for sometime with all this hoping things might change one fine day. On that fine day when you ask your seniors about your aspirations/goals, you can be sure, that they’ll be taken aback. When you tell them, you’ve learnt/seen enough that’s possible to be learnt/seen in your current profile & you need to move on; you are told that huge efforts/resources have been invested upon you. Dare not think of changing your current course of action, because that’s unfair. Yes, I understand that’s quite unfair.

Other Intellects appointed to help you in such situtations tell you that goals/plans you plan to achieve are quite a grey area and you should stay away from it, for the simple reason, you are a novice. And unluckily, if you’re not, then stay far away all the more, for all your acquired knowledge from past shall be of no relevance in your new chosen field.

You are told to follow the process & continue in the field chosen by them for you. Such tenure is long enough to cast your career in that particular competency. Employees do raise these concerns to the management, but none of us have seen any remedial action/improvements on their part. Some people accept it as their fate and continue spending their efforts/time like that. Others put up papers in the company as and when they feel they’ve got a better oppurtunity.

But, neither the organization nor the people in auhtority are ready to change. For they continue to earn profits after all this. Another person is hired in as a replacement & process resumes. So, who’s bothered to take into account complicated issues like career shift?

As per my experience, people in authority & HR Department as a whole are the real culprits, for all this nonsense.

Monday, December 05, 2005

Bangalore begins

I’d got a lot of things to do in Bangalore right after my arrival. Here’s the itinerary for the 1st week:

Day 0: First outing was to lodge a complaint for a stolen mobile phone at a Mobile Operator’s Office. Got drenched again the very first day in Bangalore, but I wasn’t the only one, both my friends were wet too. Thanks to the Hotel Manager, we had a rough time all night.

Day 1: We spent the half-day arranging for an accomodation & evening at a Police Station for compromising city traffic rules due to urgency & ended up paying 1800 bucks. Not to forget, saw the best shopping mall of the city, only from outside.

Day 2: I could be located at local-market shops buying household goods & trying to spend-n-save the money at the same time.

Days 3-7: No, I’m not that bad; same Unix basics training again?? Ok, forget me, even those who started learning Unix at Pune aren’t that bad???

After a few days, I got to know about my project; I’d got a project of one of the top Telecom major in the world. I was happy, that finally things would settle now.

I joined the project next week, and started with the Induction Programmes after 2 weeks. Office is good, rather the best company office in the city. Development team has just been formed after my joining (I’m the only person in my team :P).

Sitting in the office of the biggest software firm, working for probably the biggest telecom company makes me feel good.

Sunday, October 23, 2005

Prabhat Lodge

I began with Prabhat Lodge in Deccan area, when I came to Pune. All blessings I had gathered were washed away with the Day 0 rain in Pune. There were a lot of changes happening in/around my life. The place, people, food and the way I'd been living for sometime in past.

I joined for my job on 3rd Oct, and changes in life had shocking effects from now on. I was put into Testing Domain now, though something grand was not expected, Testing was not expected either. I thought it was time to change the blog title from "LAMP : My tryst with Web Development" to "Testing : Computer Programs/Patience of Human beings". Thankfully, I didn't have Internet access those days.

After 1 week, my competency was changed for another 4 days and after that it was changed again. And then no one believes me when I say, I'm smart; 3 competencies in 3 weeks.

Changes in lifestyle, food, people & place was acceptable/bearble, but the change of competency thrice in 3 weeks was too much for me. But for my sake, the stay at Pune was brief and I was relocated to B'lore after 3 weeks.

Anyway in my last week of stay at Pune, I started with 1 week training in Unix Basics where I re-learned the basic concepts in Unix. I found a good link to pass on to my fellows in training who were completely unfamiliar with Linux/Unix.

I've come down to B'lore after a roughly 3 weeks stay at Pune. Now, I'll be working in Unix, C, C++ domain supposedly, but so I was supposed to work in other competencies previously.

Thursday, September 01, 2005

End of an eon

Finally got email from the HR guy, that I'll be joining my first company at Pune. That means, I need to leave my home and go to Pune. Though I was not expecting to stay in Delhi after joining, Pune was not expected either. But things like these have been happening to me all the time.

I have been expecting that I'll be offered one or the other thing in Java only, as it is total greek to me and dearest to this company (more than it would have been to Sun Inc.) Though I had upgraded my RAM, installed Eclipse and found some java tutorials as well but did nothing apart from that.

Totally unrelated to this, I finally completed the FreeBSD download and tried to install it in place of Mandrake 10.1. I was trying to install it over the ext3 partitions of Mandrake only but encountered Errors about Disk Geometry. Followed this link:

But it couldn't resolve my problem. As I didn't want to risk my system, I thought of installing Fedora Core 3 only.

Some packages need to be installed on FC3 to make it work the way I do. I followed this link : and installed the necessary packages, rest all is left to smartpm.
Running FC3 out of box was quite slow for me, stopped the unrequired services, but still Gnome is quite slow. I guess time for me to go back to WindowMaker.

Wednesday, July 20, 2005


It's been a long time now, I posted over here; but today I was trying to recollect the changes happened during all this time, since I last posted.

The most important/bad being the result of the final semester, god damn it!! I scored all time worst score at college. That Professor, she proved a bigger trouble at the end (The most unexpected thing I ever had in my mind ).

Nothing much to do these days, so I've been reading some blogs, News & mailing lists most of the time. Lot of interesting stuff is being planned these days, like Yahoo/Hotmail going for AJAX based Interface of their email-programs, Blogosphere exploding like anything (Yahoo 360/MSN Spaces).

And then this new concept of centralizing the most routine information at one place, is being actively pursued now.
Microsoft launched Windows Live Program, Google launched Personalized Home Page and Yahoo already offered similar utilities along with Yahoo Mail and My Yahoo.

Time is also nearing my joining at a reputed IT Corporate on 3rd Oct. :)

Thursday, April 21, 2005

Time to compete

I've just completed my industry training at my office required in the final semester & completed PGDCA. Now, there is this huge task of completing the Project report & presentation for the submission. Haven't spoken to my professor regarding the project report even once, may be I'll give it straight to her for review.

Though I've worked on mutiple projects during my training, I can claim about one only and there is not much about that I can discuss...anywayz :)

I'll be expected to talk about the platform I worked on, reason of choosing that platform (which I'd answer by saying that company works on this technology the choice was quite obvious) and other competing technologies and stuff...

No wonder, junta would prepare a mile-long project report...and I'll also try my level best to that I'll be at home!!!