[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Combination of full outer join and left outer join

I have attached a query,

I want to understand two things from the query.
1. what will be output of the query. Need an outline, like for eg, all records from table a joined with table B then unmatched  records from table b.

2. What will be the output when i replace "join"(line 30) with right  join.

Great, if someone will help me.
query.txt
0
sakthikumar
Asked:
sakthikumar
  • 3
1 Solution
 
sakthikumarAuthor Commented:
you can ignore the where clause & group by clause in the bottom.

I just need explanation on join part.
0
 
sakthikumarAuthor Commented:
dear experts,

why this question is neglected.?

I just want to know when different joins are there, and need to know the precedence of execution.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me take this pseudo-syntax as reference:
SELECT <some columns>
  FROM <table A> A
  <JOIN TYPE> JOIN <table B> B
     ON <join condition>

Open in new window

Example:
SELECT <some columns>
  FROM Countries A
  <JOIN TYPE> JOIN Languages B
     ON A.MainLanguageID = B.ID

Open in new window


A simple JOIN (also referred to a s INNER JOIN) will match the 2 tables on the join condition, so that the result will only contain records where the join condition is fulfilled, so both tables have a record that match on the condition.
from the example, it will return all countries matched to their languages. I a country did not have set any MainLanguageID value, or that value would not be in the Languages table, that country record would not be returned. SImilary, languages that are not used as main language in any country (Latin would be a practical example here) would not be returned in any of the records

using a LEFT JOIN (or LEFT OUTER JOIN) above would solve the problem of the countries that do not have a MainLanguageID set or the value not being the languages list. any columns from Language table in the SELECT list would be returned as NULL
To find all countries that actually as "missing" the link:
SELECT A.*
  FROM Countries A
  <JOIN TYPE> JOIN Languages B
     ON A.MainLanguageID = B.ID
WHERE B.ID IS NULL

Open in new window


using a RIGHT JOIN (or RIGHT OUTER JOIN) above would not hide anymore the languages that are not used as MainLanguage in any country, but I will still hide the countries where we would use the LEFT JOIN technique.
Similarly, the columns from countries columns would be returned NULL if there is no matching record

Note: using <TableA> RIGHT JOIN <TableB>  will have the same result as <TableB> LEFT JOIN <TableA>

using a FULL OUTER JOIN (or FULL JOIN) would show all the records from both tables, so all countries even if there is no match in languages, and all languages even if no match in countries.


Important for outer joins is that if you apply a WHERE clause to the outer joined table, it will become a INNER JOIN , except if you test for the IS NULL as shown above to actually find the non-matching records.
SELECT A.*
  FROM Countries A
  LEFT JOIN Languages B
     ON A.MainLanguageID = B.ID
WHERE B.Language like 'G%'

Open in new window

will return the same as
SELECT A.*
  FROM Countries A
  INNER JOIN Languages B
     ON A.MainLanguageID = B.ID
WHERE B.Language like 'G%'

Open in new window



so far for a query with a single JOIN. In your code, you have several JOINS, and you simply have to "apply" them in the order they are listed...

SELECT ...
FROM GCB_DBA.GCB_CARGO_CAT_MAPPING M
LEFT JOIN GCB_DBA.GCB_CARGO_CAT_MAPPING_DTLS MD
  ON (M.MAPPING_ID = MD.MAPPING_ID and md.is_valid = 1 and M.STATUS IN ('A', 'P'))
LEFT JOIN GCB_DBA.GCB_CARGO_CATEGORY CC
   ON (CC.CARGO_CATEGORY_ID = MD.CARGO_CATEGORY_ID)
FULL OUTER JOIN GENCARGO.VEHICLELIST_DSCH V
         ON (V.BOL_NO = M.BOL_NO AND V.ROTATION_NO = M.ROTATION_NO AND (V.WEIGHT = MD.WEIGHT / MD.QUANTITY))
JOIN (SELECT DISTINCT * FROM GENCARGO.TRACK_DISCH_LIST) T
    ON (   (V.BOL_NO IS NOT NULL AND T.BOL_NO = V.BOL_NO AND
               T.ROTATION_NO = V.ROTATION_NO AND V.WEIGHT IS NOT NULL) 
            OR (M.Mapping_Id IS NOT NULL AND T.ROTATION_NO = M.ROTATION_NO AND
               T.BOL_NO = M.BOL_NO AND M.BOL_NO IS NOT NULL AND
               CC.CARGO_CATEGORY_DESC = 'RORO-Self propelled unit')
          )
 WHERE T.CCM_FLAG = 'N'

Open in new window


NOTE: I would apply the WHERE clause here as additional JOIN clause:
SELECT ...
FROM GCB_DBA.GCB_CARGO_CAT_MAPPING M
LEFT JOIN GCB_DBA.GCB_CARGO_CAT_MAPPING_DTLS MD
  ON (M.MAPPING_ID = MD.MAPPING_ID and md.is_valid = 1 and M.STATUS IN ('A', 'P'))
LEFT JOIN GCB_DBA.GCB_CARGO_CATEGORY CC
   ON (CC.CARGO_CATEGORY_ID = MD.CARGO_CATEGORY_ID)
FULL OUTER JOIN GENCARGO.VEHICLELIST_DSCH V
         ON (V.BOL_NO = M.BOL_NO AND V.ROTATION_NO = M.ROTATION_NO AND (V.WEIGHT = MD.WEIGHT / MD.QUANTITY))
JOIN (SELECT DISTINCT * FROM GENCARGO.TRACK_DISCH_LIST) T
    ON (   (V.BOL_NO IS NOT NULL AND T.BOL_NO = V.BOL_NO AND
               T.ROTATION_NO = V.ROTATION_NO AND V.WEIGHT IS NOT NULL) 
            OR (M.Mapping_Id IS NOT NULL AND T.ROTATION_NO = M.ROTATION_NO AND
               T.BOL_NO = M.BOL_NO AND M.BOL_NO IS NOT NULL AND
               CC.CARGO_CATEGORY_DESC = 'RORO-Self propelled unit')
          )
    AND T.CCM_FLAG = 'N'

Open in new window


so, your main table is this:
FROM GCB_DBA.GCB_CARGO_CAT_MAPPING M

you apply the 2 LEFT JOINS, then 1 FULL OUTER JOIN, and finally a JOIN (which likely could/should be a LEFT JOIN also)
0
 
sakthikumarAuthor Commented:
Thanks.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now