LEFT OUTER JOIN with subquery syntax
Lets build this up slowly.
First, lets see about getting just the information about stars:
SELECT name AS starName, (class + 7) * intensity * 1000000 AS starTemp
FROM Stars
WHERE starId < 100
(this should look might familiar!)
We get a list of all stars whose starId
is less than 100 (the WHERE
clause), grabbing the name and calculating temperature. At this point, we don't need a disambiguating reference to source.
Next, we need to add planet information. What about an INNER JOIN
(note that the actual keyword INNER
is optional)?
SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
Planets.name as planetName
FROM Stars
INNER JOIN Planets
ON Planets.starId = Stars.starId
WHERE Stars.starId < 100
The ON
clause is using an =
(equals) condition to link planets to the star they orbit; otherwise, we'd be saying they were orbiting more than one star, which is very unusual! Each star is listed once for every planet it has, but that's expected.
...Except now we have a problem: Some of our stars from the first query disappeared! The (INNER) JOIN
is causing only stars with at least one planet to be reported. But we still need to report stars without any planets! So what about a LEFT (OUTER) JOIN
?
SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
Planets.name as planetName
FROM Stars
LEFT JOIN Planets
ON Planets.starId = Stars.starId
WHERE Stars.starId < 100
... And we have all the stars back, with planetName
being null
(and only appearing once) if there are no planets for that star. Good so far!
Now we need to add the planet temperature. Should be simple:
SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
Planets.name as planetName, starTemp - (50 * Planets.orbitDistance) as planetTemp
FROM Stars
LEFT JOIN Planets
ON Planets.starId = Stars.starId
WHERE Stars.starId < 100
...except that on most RDBMSs, you'll get a syntax error stating the system can't find starTemp
. What's going on? The problem is that the new column alias (name) isn't (usually) available until after the SELECT
part of the statement runs. Which means we need to put in the calculation again:
SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
Planets.name as planetName,
((Stars.class + 7) * Stars.intensity * 1000000) - (50 * Planets.orbitDistance) as planetTemp
FROM Stars
LEFT JOIN Planets
ON Planets.starId = Stars.starId
WHERE Stars.starId < 100
(note that the db may actually be smart enough to perform the starTemp
calculation only once per-line, but when writing you have to mention it twice in this context).
Well, that's slightly messy, but it works. Hopefully, you'll remember to change both references if that's necessary...
Thankfully, we can move the Stars
portion of this into a subquery. We'll only have to list the calculation for starTemp
once!
SELECT Stars.starName, Stars.starTemp,
Planets.name as planetName,
Stars.starTemp - (50 * Planets.orbitDistance) as planetTemp
FROM (SELECT starId, name AS starName, (class + 7) * intensity * 1000000 AS starTemp
FROM Stars
WHERE starId < 100) Stars
LEFT JOIN Planets
ON Planets.starId = Stars.starId
Yeah, that looks like how I'd write it. Should work on essentially any RDBMS.
Note that the parenthesis in Stars.starTemp - (50 * Planets.orbitDistance)
is only there for clarity for the reader, the meaning of the math would remain unchanged if they were removed. Regardless of how well you know operator-precedence rules, always put in parenthesis when mixing operations. This becomes especially beneficial when dealing with OR
s and AND
s in JOIN
and WHERE
conditions - many people lose track of what's going to be effected.
Also note that the implicit-join syntax (the comma-separated FROM
clause) is considered bad practice in general, or outright deprecated on some platforms (queries will still run, but the db may scold you). It also makes certain things - like LEFT JOIN
s - difficult to do, and increases the possibility of accidently sabotaging yourself. So please, avoid it.
How to write subquery inside the OUTER JOIN Statement
You need the "correlation id" (the "AS SS" thingy) on the sub-select to reference the fields in the "ON" condition. The id's assigned inside the sub select are not usable in the join.
SELECT
cs.CUSID
,dp.DEPID
FROM
CUSTMR cs
LEFT OUTER JOIN (
SELECT
DEPID
,DEPNAME
FROM
DEPRMNT
WHERE
dp.DEPADDRESS = 'TOKYO'
) ss
ON (
ss.DEPID = cs.CUSID
AND ss.DEPNAME = cs.CUSTNAME
)
WHERE
cs.CUSID != ''
How to use subquery in outer join condition
I used the following solution which I figured out myself
SELECT [...],
c.customer_id,
(select o.order_id from orders where o.customer_id = c.customer_id
AND o.create_dt = (
SELECT MAX(create_dt) FROM orders o2 WHERE o2.customer_id =
c.customer_id))
as order_id
FROM customers c
LEFT OUTER JOIN [...]
LEFT OUTER JOIN [...]
Basically this achieves the same results I tried to to achieve with join. Why I can't use the subquery in the join, but in the column I can? What are generally pros and cons using join versus subquery for the column performance wise and otherwise? In which scenarios should I use one or another?
I think the join I tried (which didn't work) is more declaratively expressing what I'm trying to achieve, and it's a shame that Oracle doesn't support the subquery there. What is the reason for this restriction?
SQL Left Outer Join on Subquery
The "Not group by expression" error is very easy to check.
Just compare SELECT expressions with GROUP BY expressions:
SELECT DTA.trading_code Account,
OpStats.product_dwkey Platform,
SUM(OpStats.risk_amount_adj)/1000000 OpStatsVol,
RegSplits.Volume RegSplitsVol
FROM ......
......
GROUP BY DTA.trading_code,
OpStats.product_dwkey;
There are two elements in SELECT that are not in GROUP BY:
SUM(OpStats.risk_amount_adj)/1000000 OpStatsVol
RegSplits.Volume RegSplitsVol
The number 1 is OK - it's an aggregate function, it cannot be in GROUP BY.
The number 2 caused this error - it's not an aggregate function, and it is not listed in GROUP BY clause.
Oracle SQL left outer join on two subqueries
That's not the syntax of a LEFT OUTER JOIN
that uses "Table Expressions". Try:
select UE_invoice.InvoiceNo,
UE_invoice.DueDate,
UE_payment.CheckNo,
UE_payment.Amount
from (
select tblTransaction.InvoiceNo,
tblTransaction.DueDate,
tblTransaction.CheckNo,
tblTransaction.Amount
from tblTransaction,
tblTransactionType
where
tblTransaction.Type = tblTransactionType.TransactionType
and
tblTransaction.Date >= '01/01/2018'
and
tblTransaction.Type = -86
) UE_payment
Left outer join (
select tblTransaction.InvoiceNo,
tblTransaction.DueDate
from tblTransaction,
tblTransactionType
where
tblTransaction.Type = tblTransactionType.TransactionType
and
tblTransaction.Date >= '01/01/2018'
and
tblTransaction.Type = -88
) UE_invoice on UE_invoice.InvoiceNo = UE_payment.InvoiceNo
LEFT JOIN multiple sub queries
You can do it with conditional aggregation:
SELECT p.PK_Product_ID AS Product_ID,
SUM(IIF(YEAR(o.OrderDate) = 2013, 1, 0)) AS 2013_Orders,
SUM(IIF(YEAR(o.OrderDate) = 2013, p.UnitPrice * od.Quantity, 0)) AS 2013_Gross_Value,
SUM(IIF(YEAR(o.OrderDate) = 2014, 1, 0)) AS 2014_Orders,
SUM(IIF(YEAR(o.OrderDate) = 2014, p.UnitPrice * od.Quantity, 0)) AS 2014_Gross_Value
FROM (Products AS p LEFT JOIN [Order Details] AS od ON od.FK_Product_ID = p.PK_Product_ID)
LEFT JOIN (SELECT * FROM Orders WHERE YEAR(OrderDate) IN (2013, 2014)) AS o ON od.FK_Order_ID = o.PK_Order_ID
GROUP BY p.PK_Product_ID
If you want only the products ordered in the years 2013 and/or 2014 then you can change the LEFT
joins to INNER
joins.
How to use join and union for sub queries in mysql
You need to make a couple of changes in order for this query to work:
- Wrap inner
SELECT
queries into anotherSELECT *
query to makeJOIN
work, e.g.() SP LEFT JOIN () SP
is not a valid syntax. Instead, useSELECT * FROM (..) SP JOIN (..) FP
- Remove
SP.*
from outerSELECT
asSP
andFP
are only visible to inner queries, useSELECT *
instead - Remove
WHERE SP.Service=FP.Service
from outerWHERE
clause as again,SP
andFP
won't be visible.
The below query should work:
SELECT *
FROM (
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00'
GROUP BY Service) SP
RIGHT JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00'
GROUP BY Service) FP USING (Service))
UNION ALL
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00'
GROUP BY Service) SP
LEFT OUTER JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00'
GROUP BY Service) FP USING (Service) )) as tbl2
GROUP BY Service
Order BY Service
update
You can't use function in USING
clause, so you need to alias that column and use the alias in USING
, e.g.:
SELECT *
FROM (
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00'
GROUP BY DAYNAME(Dataime)) SP
RIGHT JOIN
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00'
GROUP BY DAYNAME(Dataime)) FP USING(`day`))
UNION ALL
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00'
GROUP BY DAYNAME(Dataime)) SP
LEFT OUTER JOIN
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00'
GROUP BY DAYNAME(Dataime)) FP USING (`day`) )) as tbl2
GROUP BY `day`
Order BY `day`
Referencing outer query's tables in a subquery
i think that won't work, because you're referencing your derived table 'c' as part of a join.
however, you could just take out the WHERE p.user = u.id
though and replace with a GROUP BY p.user
in the derived table, because the ON c.user = u.id
will have the same effect.
Join on TOP 1 from subquery while referencing outer tables
If that is the logic you want, you can use OUTER APPLY
:
SELECT C.ContactSys, GL.ABC, GL.XYZ,
... a bunch of other columns ...
FROM Users U JOIN
Contacts C
ON U.ContactSys = C.ContactSys LEFT JOIN
UserWatchList UW
ON U.UserSys = UW.UserSys LEFT JOIN
Accounts A
ON C.AccountSys = A.AccountSys OUTER APPLY
(SELECT TOP 1 gl.*
FROM GuestLog gl
WHERE gl.ContactSys = C.ContactSys
ORDER BY gl.GuestLogSys DESC
) GL
WHERE C.OrganizationSys = 1012 AND
U.UserTypeSys = 2 AND
C.FirstName = 'steve'
Related Topics
Postgres - Create Table from Select
Cumulating Value of Current Row + Sum of Previous Rows
Sql: Is There a Possibility to Convert Numbers (1,2,3,4...) to Letters (A,B,C,D...)
SQL Join, Group by on Three Tables to Get Totals
Iterate Through Rows in SQL Server 2008
Postgresql Generate_Series of Months
Deleting Hierarchical Data in SQL Table
SQL Bulk Insert with Firstrow Parameter Skips the Following Line
Django: Using Custom Raw SQL Inserts with Executemany and MySQL
Command for Adding a Default Constraint
SQL Server 2016, Invalid Object Name 'String_Split'
Oracle SQL: Use Sequence in Insert with Select Statement
Where Col1,Col2 in (...) [SQL Subquery Using Composite Primary Key]