Left Join Vs Left Outer Join Syntax Generates Different Execution Plans
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
Hello! I need to write a query using left outer join and I'm having trouble with it. I have 2 tables:customer and cust_info. I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table. But I need one condition in this query:only records from customer table where cus_type in ("A","B","C"). I don't need all other types ("D","E").
So my query looks like this:
select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status from customer left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk and cus_type in ("A","B","C") AND cus_info.cus_table = 'CUSTOMER'
The result should be like this:
cus_name cus_address cus_type status Amoco 457 bent A new Bingo 47 lone oak C NULL Sears 1020 Magic dr. B exist
But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.
a job can be done with 1 or more operators I want to be able to show user both assigned and unassigned operator on the front end on a certain job so I haev only parm being passed in @jobID.
How do I got about showing all the operators but somehow differentiate ones already assigned to job
SELECT e.rec_id,e.EMP_ID,e.FIRST_NAME,e.PROJECT_ID, e.DEPARTMENT_CODE, e.POSITION_CODE,e.WORKING_STATUS, d.department_name,p.position_name, c.country_code, c.country_name FROM employee e LEFT OUTER JOIN position p ON e.position_code = p.position_code LEFT OUTER JOIN department d ON e.department_code = d.department_code left outer join country c ON e.country_code = c.country_code
where I use where e.working_status <>'left company'
it retreive few records, it shoud display all records except where e.working_status <> 'left company' i.e. left outer join is not working,
we have approx 1000 employee records, 100 employee left the company. it should show 900 employee records but is show few records, how can use where clause in left outer join it show all records except where e.working_status <> 'left company'
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three"Left Outer Joins" in order to return every transaction for a specificset of criteria.Using three "Left Outer Joins" slows the system down considerably.I've tried creating a temp db, but I can't figure out how to executetwo select commands. (It throws the exception "The column prefix'tempdb' does not match with a table name or alias name used in thequery.")Looking for suggestions (and a lesson or two!) This is my first attemptat SQL.Current (working, albeit slowly) Query BelowTIASELECTLEDGER_ENTRY.entry_amount,LEDGER_TRANSACTION.credit_card_exp_date,LEDGER_ENTRY.entry_datetime,LEDGER_ENTRY.employee_id,LEDGER_ENTRY.voucher_explanation,LEDGER_ENTRY.card_reader_used_ind,STAY.room_id,GUEST.guest_lastname,GUEST.guest_firstname,STAY.arrival_time,STAY.departure_time,STAY.arrival_date,STAY.original_departure_date,STAY.no_show_status,STAY.cancellation_date,FOLIO.house_acct_id,FOLIO.group_code,LEDGER_TRANSACTION.original_receipt_idFROMmydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,mydb.dbo.FOLIO FOLIOLEFT OUTER JOINmydb.dbo.STAY_FOLIO STAY_FOLIOONFOLIO.folio_id = STAY_FOLIO.folio_idLEFT OUTER JOINmydb.dbo.STAY STAYONSTAY_FOLIO.stay_id = STAY.stay_idLEFT OUTER JOINmydb.dbo.GUEST GUESTONFOLIO.guest_id = GUEST.guest_idWHERELEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_idAND FOLIO.folio_id = LEDGER_TRANSACTION.folio_idAND LEDGER_ENTRY.payment_method='3737******6100'AND LEDGER_ENTRY.property_id='abc123'ORDER BYLEDGER_ENTRY.entry_datetime DESC
What is the result of adding an or into a left outer join?For example:Selectl.list_id,l.request_idFrom List lleft outer join Request ron l.request_id = r.request_id or l.list_id = r.list_idThanks :)
I am trying to get a query to work and having trouble. I have two tables. I want all records in my left table plus null in the right table if no matching records exist or one record only if there are matching records. What I don't want is multiples if there is more than one match in the right table.
So if I use this: select * from contacts left outer join sales on contacts.id = sales.contactid
I will get more than one record per contact if there is more than one sale. I only want one record per contact even if there is more than one sale.
Greetings! I have a problem converting *= to LEFT OUTER JOIN. Here's my code that will retrieve a single record when executed:
SELECT * FROM ar_t_memo_hdr A, ar_r_trbal_cust_tmp B, common..fs_currency_master E, ar_reval_history rev
WHERE A.company_no = 'RAM' AND A.company_locn = 'BANG' AND E.fs_company_code = 'RAM' AND E.fs_locn_code = 'BANG' AND A.currency = E.fs_curr_code AND A.memo_type = 'DM' AND A.cust_no = B.cust_no AND A.so_locn = B.so_locn AND B.host_id = 100 AND A.memo_dt <= '2006-09-25 00:00:00.000' AND A.unapplied_amt >= 0 AND ( (CONVERT(varchar,A.reversal_dt,101) > '2006-09-25 00:00:00.000' AND A.status = 'V') OR (a.status ='A') ) AND A.company_no *= rev.company_no AND A.company_locn *= rev.company_locn AND A.cust_no *= rev.cust_no AND B.cust_no *= rev.cust_no AND B.so_locn *= rev.so_locn AND A.memo_type *= rev.document_type AND A.memo_no *= rev.document_no AND rev.created_date = (SELECT MAX(his.created_date) FROM ar_reval_history his WHERE his.company_no = rev.company_no AND his.company_locn = rev.company_locn AND his.document_no = rev.document_no AND his.document_type = rev.document_type AND his.rev_dt <= DATEADD(dd,1,'2006-09-25 00:00:00.000'))
After replacing *= with LEFT OUTER JOIN, the above query returns nothing. Could some one please help me resolve the problem??
Need some assist - not sure I am using right join? I am using a Left Outer join - Table A - has Cust # and Cust Name Table B - has Cust #
If there are 2 entries with same Cust # and a slight difference in spelling of cust name, I get 2 entries. Looking to get 1 row based on cust #, regardless of spelling of name?
Please help... I've got Two tables, Problem and Breach, in DB Support. The main Table (Problem) has a primary key on field Number and I retrieve most of my data from this table but the other fields are irellevant. The Breach Table includes fields: Number & Keyword. I use an Left Outer Join to connect from Problem.Number to Breach.Number and retrieve the Breach.Keyword if any. If there's no records in Breach it still retrieves all the Problem.Numbers and that's great but when there's more than one Breach.Keyword for the Problem.Number I retrieve more than one record at this stage but I would only like to retrieve one. My Query:
Select
Support_Problem.Number, Breach.Keyword
From
Support_Problem Support_Problem
Left Join Support_Breach Support_Breach ON Support_Problem.Number=Support_Breach.Number
Having a little trouble with this sql... UPDATE tblCalibrationReview SET poNumber = CalibrationReview.[PONumber], calCertNumber = CalibrationReview.[CalCertNumber], q1Review = (CASE CalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END), followUp = CalibrationReview.[Followup] FROM CalibrationReview LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID WHERE CalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID The left outer join. I need to read CalibrationReview.EquipmentNumber, look at tblEquipments and get the ID field where that number matches and update tblCalibrationReview.equipmentId with that number. I'm getting the error when running: The column prefix 'tblCalibrationReview' does not match with a table name or alias name used in the query. Thanks for any input, Zath
my table isCustomer Customer Id--------- ----------------Mary 1Jhon 2Anna 3OrderId CustomerId Product ProductDesc------- --------- --------- -----------1 1 video bla bla2 1 tv bla bala3 2 video bla bla4 2 cd bla blaI want to see-------marry tv bla blaJohn cd bla blaanna
I need a small help in creating a join query in sql.
My two tables are A and B. A has got columns id, dname, counter and date. B has got two columns link and date. The primary key of A is the column Id whereas in table B, both are primary keys(composite key).
I need to get the records (id,dname,counter) from A which has no corresponding link in table B.
To be precise, If the table A has fields (id,dname,counter) (1,abc,2000) and if table B has no record for abc, this row should be returned from A.I hope using left outer join will help me in getting my desired result.I hope someone will be able to help me out.
Hi, I want to get join of two tables A and B in such a way that all the Data from A for a particular Date should be extracted and then from table B Reqired column should selected against that data (it can be null). But if i use the following query: 1) Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same Where A.Date = '1/22/2008' it first joins the Table A and B (A contains millions of records) which takes a lot of time and then where is applied which takes a lot of time. And if I use the following query: 2)Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same AND A.Date = '1/22/2008' in this case A.Date = '1/22/2008' has no effect and all the data from the table A comes. I have also tried first making the temp table for A.Date = '1/22/2008' and the joining with B but its cost is same as 1. For this purpose Oracle allows the use of (+) operator like: 3)Select A.*,B.Reqired from A,B Where A.Same=B.Same(+) AND A.Date = '1/22/2008' which means all data from A for '1/22/2008' Plus B.Required against it whether its null or not. Please let me know if there is a way around in MS SQL SERVER 2005. Thanx.
Dear All, I am not sure this is the correct forum for my issue, still im going on..... I have a sql query which uses left outer join and where clause select I.ir_id, I.ir_title,I. ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' order by I.ir_id desc this works perfect. Now I want to exclude few records from the result on condn 'bstatus <> 'Yes'. So now my query will be select I.ir_id, I.ir_title, I.ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' and B.bstatus <> 'Yes'order by I.ir_id desc The below query will not return the left table records which doesnt have a matching one in the right table. It works something like inner join. Could you please anyone tell me,where I am wrong? Thanks, Girija
Hi friends,i've one table as pro_area. it consist two fields pro_area_key and pro_area_name.i've second table as pro_access it consist three fields pro_access_key, pro_access_name, user_key and pro_area_key.i've perform the left outer join on both these tables, query is as followSelect pa.pro_area_key, pa.pro_area_name from pro_area pa left outer join pro_access pr on pa.pro_area_key = pr.pro_area_key this query gives perfect result but now i want the same result according to user_keylike i can give user_key fields valueany help for it,Thanx in advance
Hello ! I want to make two left outer joins. The first on table A with table B and the second on table B with table C. The problem is that an error occurs and it tells me that left outer join on a field that is part of a left outer join is prohibited.
This is what i want : select a.product , b.description , c.description from products as a , media as b , topics as c where a.media*=b.media and b.topic*=c.topic
or
select a.product , b.description , c.description from products as a left outer join b on a.media=b.media left outer join c on b.topic=c.topic
SELECT CO.CO_ID, MAX(PR.DOCNO) AS pO_DOCNO ,MAX(CR.DOCNO) AS CR_DOCNO FROM COMPANY CO lEFT OUTER JOIN PROCUREMENT PR ON OG.CO_ID=PR.CO_ID lEFT OUTER JOIN CONTRACT CR ON CO.CO_ID=CR.CO_ID GROUP BY OG.CO_ID
the result is ok but the problem its taking infinte time if i add more tables to the outer join, i have more tables and each with huge number of records
any better way to do this ?? its true performance is a big deaaaal
I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx. I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.
This is my code, based on 2 temporary tables I have previously populated:
from #budgets b left outer join #transactions t on t.accountnumber=b.accountnumber And right(t.accountnumber,4) > 7149 and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001') order by b.accountnumber
I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.
I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.
We've got this whole mess trying to export data from Lotus Notes toGreat Plains. As part of the process, we need to ensure that a Notesrecord is exported only once, but have the ability to re-export it ifsomething breaks. So, for that reason (and some others) I decided touse a simple log table:create ExpLog (ExpDateTime Datetime not null,DBID varchar(16) not null,docUNID varchar(32) not null,)So, the code records the records datetime, and the DBID and docUNID(which uniquely identifies every Lotus Notes "record"). My question isin the sql that exports the records. Which is likely to be moreefficient?selectfield1,...from LotusNotes LN left outer join ExpLog EL on LN.DBID = EL.DBID andLN.docUNID = EL.docUNIDwhere EL.docUNID is nullorselectfield1,...from LotusNotes LNwhereln.DBID not in (select DBID from EL)andln.docUNID not in (select docUNID from EL)How would one go about figuring this out assuming that the SQL is goinginto a DTS package. (I think that means the plan isn't saved?)Thanks.
I am stuck in a bit of a mess, and i really don't know how to get out of it.
I am a dot.net developer, working with MS SQL server 2000.
I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.
select Stock_code, description from stock
Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1 and order it by delivery_date desc.
Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'
select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where stock_code='E0016/6' and qty_order>qty_delivered order by delivery_date desc
But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...
i.e
select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock
left outer join
(select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where
--Can't say this(stock_code= stock.stock_code and ) qty_order>qty_delivered order by delivery_date desc) as tempp
on tempp.stock_code=stock.stock_code
Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...
OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!
Hope someone can help me.
Regards,
Munira
ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.
I think it is quite often when you need to view some records, which refer (by key) to data in other tables. For instance, a user belongs to a group but it is preferable to show group name in the user data rather than group id. The options are
1) LEFT OUTER JOIN: SELECT users.id, groups.name FROM users LEFT OUTER JOIN groups ON users.[group] = groups.id
2) A Subselect: SELECT id, [group] = (SELECT [name] FROM groups WHERE id = users.[group]) FROM users
