Practical Use for FULL OUTER JOIN & CROSS JOIN

Question

Practical Use for FULL OUTER JOIN & CROSS JOIN

Hakuna Matata

SSCertifiable

Points: 5577
More actions
March 7, 2011 at 10:55 pm

#233020

Hi All,
Where exactly can we use FULL OUTER JOIN & CROSS JOIN ? Do they have any practical use or are they just concepts? Please help?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Best_boy26 SSC-Addicted Points: 407 More actions · Answer 1

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Different SQL JOINs

JOIN: Return rows when there is at least one match in both tables

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

FULL JOIN: Return rows when there is a match in one of the tables

http://www.firstsql.com/tutor3.htm

well explained

Thanks in Advance...
trying to learn SQL Query World

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 2

Example: A full join between Member and Claims (on MemberId in both tables) would give you

1. Members that have claims (Inner)

2. Members with no claims (Left)

3. Claims with missing parent Members, i.e. orphaned claims (Right)

Does this answer your question ??

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Check the link in my sig under 'Tally Tables' for a good use of Cross Joins.

All of the 'odd' joins have their places, just should be used rarely and with a direct purpose in mind.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 4

Hakuna Matata

SSCertifiable

Points: 5577

March 8, 2011 at 11:14 pm

#1296317

Thanks a lot for the information provided.:-)

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 5

Thanks a lot for updating me with this information :-).:-)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Another good use for a cross join is when you want to create records on a 'for each' basis. A typical example would be a table of inventory locations (ImLoc) and inventory items (ImInv). The application needs to have each item at each location since it does constant updates to such things as On Hand Quantity and the app doesn't want to check for existence of an item at a location each time it does the update.

Here you's have something like:

CREATE TABLE ImLoc -- Location table

( LocID VARCHAR(10)

, LocDescr VARCHAR(20)

)

CREATE TABLE ImInv -- Inventory table

( ItemNo VARCHAR(10)

, LocID VARCHAR(10)

, QtyOnHand INT

)

-- Populate the Inventory table for each location

-- Let's say you just added ItemNo '123456' to table ImItem

-- and you have to now create the inventory records.

INSERT INTO ImInv

( ItemNo, LocID, QtyOnHand )

SELECT

I.ItemNo, L.LocId, 0

FROM ImItem I

CROSS JOIN ImLoc L

WHERE I.ItemNo = '123456'

This creates 1 inventory record for each location.

Todd Fifield

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

FULL OUTER JOINS are very useful when comparing two sets of data and you want to identify items that exist in one set but not in another. Testing for NULLS in the joined column(s) of each table will let you select only those rows who do NOT have a match.

CROSS joins are also useful for "exploding" tables (my slang for un-pivoting columns into rows).

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills