Avoiding two LEFT JOINs

join;oracle

Given these 2 tables:

CREATE TABLE aaa (
  x number primary key,
  y varchar2(10))

CREATE TABLE bbb (
  x number primary key,
  y varchar2(10))

I insert two columns:

insert into aaa values (1, 'foo')
insert into bbb values (1, 'bar')

Then, I query for:

select a.x, a.y, b.y from aaa a
LEFT JOIN bbb b on a.x = b.x

which returns:

1 foo bar

Then, I insert a row with x = 2 into bbb:

insert into bbb values (2, 'bar')

Re-running the above query returns the same result.

However, I'd like to write a query to get a result of:

1 foo bar
2 null bar

Can I achieve that generically without doing:

select b.x, b.y, a.y from bbb b
LEFT JOIN aaa a ON b.x = a.x

Best Answer

Your join clause is the issue here.

The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa.

a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb). Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb.

As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table.

Something like this should work:

SELECT COALESCE(a.x,b.x) AS x
,a.y
,b.y
FROM aaa a
FULL OUTER JOIN bbb b
    ON a.x=b.x

Related Solutions

Mysql – Optimizing multi-table left joins

PLEASE use SHOW CREATE TABLE; DESCRIBE is not descriptive enough.

You seem to be working with IPv4 IP addresses. IPv6 is upon us!

Your query inherently must do a full table scan in order to get the "total" upon which you do the ORDER BY. Since there are a million rows, it will take time.

count('*')

The quotes are not needed.

The two SELECTs are likely to be identical; order across JOIN does not matter. It does matter across LEFT JOIN. (INNER and OUTER add nothing.)

Suggestion...

First, see if this gives the correct information (albeit incomplet info):

SELECT  e.sid, e.cid, COUNT(*) AS total
FROM  event AS e
JOIN  iphdr as h ON e.sid = h.sid
                AND  e.cid = h.cid
WHERE  e.is_deleted = 0
GROUP BY  i.ip_src, i.ip_dst
ORDER BY  total DESC
LIMIT  5;

If so, then use that as a subquery, a la FROM (that-select) and LEFT JOIN to the other two tables to get the rest of the info.

The trick... The subquery is doing less work since it is touching 2 tables (vs 4). The LEFT JOINs only have to do 5 probes each, not a million.

MySQL : LEFT JOIN not fetching required data

Storing a string of comma-separated values in a column is not an ideal design; ideally you would have a table of keywords and a 2-column map table with a row for every (E_HEADER.id,keyword_id).

However, given your current structure, the FIND_IN_SET() function should work, although this will not be the best possible performance, since a function in the where clause that takes a column as its argument will generally prevent the use of indexes for evaluating that condition -- but given your structure, you don't have an alternative.

FIND_IN_SET() returns the ordinal position of the first argument within the comma-separated string that is the second argument, or in this case "something true" if a match is found; no match returns 0 ("false") and if either argument is null, the function returns NULL.

Also, it's difficult to say without a better understanding of your structure exactly what you want the query to do, but the derived table seems unnecessary.

SELECT Q.question_id, Q.school_id
  FROM Q_MASTER Q
  LEFT JOIN E_HEADER X on X.school_id = Q.school_id AND X.low_q = 1 AND X.school_id = 6
 WHERE NOT FIND_IN_SET(Q.question_id, X.keyword);

This would select every question_id and school_id from Q_MASTER where there exists a row in E_HEADER with a matching school_id and low_q is equal to 1 and school_id is equal to 6 and where the value of Q.question_id is not found in the comma-separated string X.keyword.

Based on my interpretation of the original query, only Q_MASTER records with school_id = 6 will be returned in spite of the left join, because the join requires school_id to be identical, and will project a NULL for X.keyword otherwise; FIND_IN_SET() returns NULL if either argument is null, and the expression "NOT (NULL)" returns evaluates to NULL which will be considered untrue by the WHERE clause. The query will perform much better as an inner JOIN than a LEFT [outer] JOIN because fewer rows will need to be considered, but the ultimately correct approach requires a valid logical expression of what you actually need the server to do.

Best Answer

Related Solutions

Mysql – Optimizing multi-table left joins

MySQL : LEFT JOIN not fetching required data

Related Question