Is It True That Using Inner Join After Any Outer Join Will Essentially Invalidate the Effects of Outer Join

MySQL - Why does WHERE ignore RIGHT JOIN?

What you want is:

RIGHT JOIN reel_classes AS class
    ON item.class_id = class.class_id AND
       inv.current_length  > 0;

Your question is why the RIGHT JOIN turns into an INNER JOIN with the WHERE clause.

The reason is simple. For the non-matching rows, inv.current_length is NULL and this fails the comparison.

I would also suggest that you use LEFT JOIN, starting with the table where you want to keep all the rows. Most people find it much easier to understand logic that is "keep all rows in the first table" rather than "keep all rows in some table whose name will come up".

Join a 3rd table when the result of the initial join has NULL values (that I want retained!)

Try it like this:

SELECT user.firstname, user.lastname, s.timestamp, s.race_number, s.user_id, s.count 
FROM (
    SELECT event_entry.user_id as user_id, max(timestamp) as timestamp, event_timestamps.race_number as race_number, count(event_timestamps.race_number) as count 
    from event_timestamps
    Left join event_entry on event_timestamps.race_number = event_entry.race_number and event_entry.event_id=430 
    Where timestamp > '2022-05-28 11:50:00' and timestamp < '2022-05-29'
    group by event_timestamps.race_number, event_entry.user_id
) s
LEFT JOIN user on user.user_id = s.user_id
order by s.count desc , s.timestamp

like @Marshal_c said, inner join doesn't work, cause it gets rid of NULLs, viz. SQL JOIN and different types of JOINs

also like @Honk_der_Hase points out, your code is missing group by for all columns. When your SQL works, i asume, that you work in MariaDB, which takes in this case first found element (equivalent of ORALCE's TOP())

In your scenario, you should also have some kind of integrity rule, which will prevent from having multiple users (user_id) with same race number (race_number). If you had thouse records in your database, the rows would start duplicating.

Also be aware, that some databases (like MariaDB) can have problems with table called user because table named like that is used for authentication into database (in information_scheme)

Left join then inner join incorrect result using sql?

Joins are "completed" in the order in which their ON clauses exist. Joins aren't just between tables - they're frequently between results produced from other joins. So:

select * from #temp t
left join #table_link tl on tl.temp_id=t.id
inner join  #temp2 t2 on t2.id=tl.temp2_id and t2.active_tag=1

First performs a left join between t and tl. This produces a new result set (lets call it ttl) which contains rows from t and may contain joined rows from tl or nulls (since its a left join). We then join that result (ttl) to t2, performing an inner join. However, we know that ttl may contain nulls for columns that originated from tl and so the inner join will fail.

What we can instead write is:

select * from #temp t
left join #table_link tl
inner join #temp2 t2
 on t2.id=tl.temp2_id and t2.active_tag=1
 on tl.temp_id=t.id

And note that I've now (by moving on clauses around) changed the order of the joins. We're now first performing an inner join between tl and t2 (lets call it t2tl) and then performing a left join between t and t2tl. This means that the left join acts last and so we can again get null results in the final result.

---

To see how joins and on clauses act, think of the join word as being a ( and the on clause as being ). Then you find the joins that on clauses relate to as finding matching ()s.

What is the difference between INNER JOIN and OUTER JOIN ?

Assuming you're joining on columns with no duplicates, which is a very common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

How to do FULL OUTER JOIN in MS SQL Server when columns don't share any parameter including primary key?

Your problem is that one or more table have duplicates.

In the meantime, your FULL JOIN logic is filtering out rows that you seem to want. You can simplify and improve the logic:

select coalesce(t1.id, t2.id, t3.id, t4.id, . . . ) as id,
       t1.param as param1,
       t2.param as param2,
       t3.param as param3,
       t4.param as param4,
       . . .
from single_param_table_1 t1 full join
     single_param_table_2 t2
     on t2.id = t1.id full join
     single_param_table_3 t3
     on t3.id = coalesce(t1.id, t2.id) full join
     single_param_table_4 t4
     on t4.id = coalesce(t1.id, t2.id, t3.id) full join
     . . .

That is, you need lots of use of coalesce() so the ids match across the tables.

I should note that standard SQL and most databases support the using clause which simplifies this logic. However, SQL Server does not support using.

That simplifies your logic. However, your issue is that one or more tables have duplicate ids.

---

Related Topics