-1

I want to make a view for which i will be requiring a query having self join in it. I am worried about its performance and behavior as it is going to be fetch some handsome amount of data. Details of table and query are as under (original table has more columns and i will be requiring results from multiple columns; this is just to present you with an example):-
Person Table 

PK NAME       SPOUSE_REF_ID   IS_MARRIED
1  John          Null             Y
2  Alice          1               Y
3  Varvatos      Null             Y
4  Barbara        5               Y
5  Stanely       Null             Y
6  Ross          Null             N
7  Henry         Null             N

I need to query data of those who are married and have spouse_ref_id with them. Thus the query i made is:-

select a.NAME spouse_name, a.SERVICE_NO, a.id,a.employment_category_id, pf.name name, pf.service_no,pf.id,
  pf.employment_category_id
  from person a
 inner join person pf on
  a.id = pf.spouse_ref_id
  where a.IS_MARRIED ='Y'

Please advise me regarding its optimization and if you think that restructuring query will fetch better results or self join is not advisable in queries etc etc..
I would appreciate a reply having an optimized view.

Femme Fatale
  • 870
  • 7
  • 27
  • 56
  • 1
    Do you have any problem, or a question? – JodyT Mar 14 '13 at 22:16
  • 2
    Since a self join is the only way to do this, there is not much else you can do. Sometimes you can replace a self join with an analytical function but not in this case – Nick.Mc Mar 14 '13 at 23:44

1 Answers1

-2

When you query a view, then in the background, the query that was used to construct the view is executed and then the appropriate data is returned back to you.

Sometimes, I have noticed that using a where clause with a join fetches results which are not expected. So, I suggest that you modify your original query to the following :-

select a.NAME spouse_name, a.SERVICE_NO, a.id,a.employment_category_id, pf.name name, pf.service_no,pf.id,
  pf.employment_category_id
  from person a
 inner join person pf on
  a.id = pf.spouse_ref_id
  and a.IS_MARRIED ='Y'

Another optimization which you can do is to create a (unique index)/ (index) on spouse_ref_id. This will ensure that when you are doing a self join, then it will not be a full table scan for both the instances. I am not sure if the following query might produce even better results or what you intended (but worth a shot) :-

select a.NAME spouse_name, a.SERVICE_NO, a.id,a.employment_category_id, pf.name name, pf.service_no,pf.id,
      pf.employment_category_id
      from person a
     inner join person pf on
      a.id = pf.spouse_ref_id
      and a.IS_MARRIED ='Y'
      and pf.IS_MARRIED='Y'
Max
  • 4,067
  • 1
  • 18
  • 29
  • 2
    I strongly disagree with the advice to put both predicates and join conditions in the `ON` clause. It may improve readability in some cases, but it will not save you from unexpected results. More details are required to support the assertion that an extremely common querying method should be avoided. – Jon Heller Mar 15 '13 at 03:46
  • +1 for @jonearles comment -- the only time I've found it useful to put conditions like that in the join is in outer joins. – David Aldridge Mar 15 '13 at 07:31