Q

Tips for derived tables in SQL and using FULL OUTER JOINs

SQL expert Rudy Limeback gives his strategy for using FULL OUTER JOINs on derived tables in SQL. Read the tip here.

My question is regarding FULL OUTER JOINs on derived tables. My query is as follows:

select tb1.usr,tb1.ADD,tb2.UPD from
(select usr,count(nin) ADD   
from RGS8 where act='ADD' 
and dte=20080310 group by usr)  tb1 
FULL OUTER JOIN
(select usr,count(nin) UPD   
from RGS8 where act='UPD' 
and dte=20080310 group by usr)  tb2 
ON tb1.usr=tb2.usr

The query executes successfully when using the left or the right outer joins but no results are given with the full outer joins. I wonder if there are some constraints with FULL OUTER JOINs?

I'm not sure why it isn't working for you, unless perhaps you're using MySQL, which doesn't support FULL OUTER JOIN. But in that case you should've received an error message, rather than no results.

Here's an alternate strategy:

SELECT usr
     , SUM(CASE WHEN act = 'ADD'
                THEN 1
                ELSE 0 END) AS ADD
     , SUM(CASE WHEN act = 'UPD'
                THEN 1
                ELSE 0 END) AS UPD
  FROM RGS8 
 WHERE act IN ('ADD','UPD') 
   AND dte = 20080310 
GROUP 
    BY usr

This should perform better as well, since it requires only one pass of the table.

This was last published in October 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close