10
LEFT JOIN and LEFT OUTER JOIN are synonymous so apologies but it sounds like option 2 :-)
The "Outer" join (which preserves unmatched rows) is as opposed to "Inner" (which doesn't).
There are three flavours of outer join. Left, Right, Full (dependent on which unmatched rows are to be preserved).
As SQL doesn't have direct join syntax for any other types ...
4
This would be my preferred way to get what you want, using a correlated sub-query with EXISTS. Assuming you have an index on both tables on switch, port and mac, this should be good performance as well.
SELECT *
FROM SwitchportMac_import i
WHERE NOT EXISTS (
SELECT TOP (1) 1
FROM SwitchportMac sm
WHERE sm.switch = i.switch
and sm....
3
from SwitchportMac_import i LEFT OUTER JOIN SwitchportMac sm
This returns rows in i which match in sm.
Correct. That's exactly what a LEFT [OUTER] JOIN does - it gets you everything in the "left" table (i) whether or not it can find a matching row in the "right" table (sm).
To get "new" items (those that appear in the "left" table but not the "...
1
where sm.id is null is needed to match the rows that don't exist in the join.
Left joins are always OUTER so its syntactic sugar invented by committee. They where thinking of confusing you and others ahead of time. You're not a idiot, confusion was implemented as planned.
1
I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to
produce the desired result
FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?
db <> fiddle
SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
ISNULL(T1.Date, T2.Date) AS Date,
...
1
You use a subselect query as basis for t1 and t2
Like
SELECT
t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1)
FROM
(SELECT
*
FROM
TagMap
WHERE
TagID IN (SELECT
TagID
FROM
Tags
WHERE
Articles > 50)) AS t1
JOIN
(SELECT
*
...
1
What you want is called an UPDATE JOIN
First you must create a query that has the MAX(b_foo) for every a_uid:
SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;
Making this a subquery, you can perform the UPDATE JOIN as follows:
UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;...
1
If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox.
When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both ...
1
If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):
SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID ...
1
As documented in the manual you should not repeat the target table in the FROM clause.
So your UPDATE should be like this:
UPDATE public."section" s
SET column = evt.oldcolumn
FROM public."event" evt
WHERE s.event = evt.id
AND s.id = 1;
1
Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.
From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not ...
1
I had just asked that on a different forum. The best solution for a 3rd Normal Form schema design was to select all that is needed in the linking table and compare the Cardinality (count) of the matching results.
The other forum was for Oracle. The answer you seek is the PostgreSQL version of this post: https://community.oracle.com/message/15613817#...
1
You never ANALYZEd the foreign tables, so the estimates are way off, and PostgreSQL chooses a bad execution plan.
ANALYZE tds, dq_infos;
should improve the situation.
Different from regular tables, foreign tables are not handled by autoanalyze.
1
Did you try a lateral join?
select parent.id, parent.name, q.cnt
from parent
left join lateral (
select count(*) cnt
from child ch
where ch.pid = parent.id
) q on true
where name like '%xyz%'
1
For example you can do it this way:
select p.id, p.name,
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt
from parent AS p
where name like '%xyz%'
or this:
select p.id, p.name,
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name
if you need more columns from parent:
...
1
Assuming all the columns are compatible:
SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
where table1.customer_id is null
If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.
1
All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column.
But you can make the subquery return a single column whose type is a composite type by using a row constructor:
ARRAY(SELECT ROW(name, type, description) FROM ...)
1
LEFT join says "take all rows from the left i.e. preceeding table i.e. SwitchportMac_import". You don't want all rows from that table, only the ones that do not match.
Adding the IS NULL will work. I think a NOT EXISTS would better suit your needs here.
Only top voted, non community-wiki answers of a minimum length are eligible
