Cannot Have a Qualifier in the Select List While Performing a Join W/ Using Keyword

Cannot have a qualifier in the select list while performing a JOIN w/ USING keyword

It's complaining about the oi qualifier:

SELECT oi.order_id, product_jd, order_date
^^^

Oracle does not allow qualifiers in combination with a using join. The clearest way out is using a regular join:

SELECT oi.order_id, product_jd, order_date
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id

You can also omit the qualifier. The using statement tells Oracle that even though there are two fields called order_id, they are both equal:

SELECT order_id, product_jd, order_date
FROM order_items oi JOIN orders o
USING(order_id)

Oracle USING clause best practice

You're presumably already aware of the distinction, but from the documentation:

ON condition Use the ON clause to specify a join condition. Doing so
lets you specify join conditions separate from any search or filter
conditions in the WHERE clause.

USING (column) When you are specifying an equijoin of columns that
have the same name in both tables, the USING column clause indicates
the columns to be used. You can use this clause only if the join
columns in both tables have the same name. Within this clause, do not
qualify the column name with a table name or table alias.

So these would be equivalent:

select e.ename, d.dname
from emp e join dept d using (deptno);

select e.ename, d.dname
from emp e join dept d on d.deptno = e.deptno;

To a large extent which you use is a matter of style, but there are (at least) two situations where you can't use using: (a) when the column names are not the same in the two tables, and (b) when you want to use the joining column:

select e.ename, d.dname, d.deptno
from emp e join dept d using(deptno);

select e.ename, d.dname, d.deptno
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

You can of course just leave off the qualifier and select ..., deptno, as long as you don't have another table with the same column that isn't joined using it:

select e.ename, d.dname, deptno
from emp e join dept d using (deptno) join mytab m using (empno);

select e.ename, d.dname, deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined

In that case you can only select the qualified m.deptno. (OK, this is rather contrived...).

The main reason I can see for avoiding using is just consistency; since you sometimes can't use it, occasionally switching to on for those situations might be a bit jarring. But again that's more about style than any deep technical reason.

Perhaps your colleague is simply imposing (or suggesting) coding standards, but only they will know that. It also isn't quite clear if you're being asked to change some new code you've written that is going through review, or old code. If it's the latter then regardless of the reasons for them preferring on, I think you'd need to get a separate justification for modifying proven code, as there's a risk of introducing new problems even when the modified code is retested - quite apart from the cost/effort involved in the rework and retesting.

A couple of things strike me about your question though. Firstly you describes the on syntax as 'old-fashioned', but I don't think that's fair - both are valid and current (as of SQL:2011 I think, but citation needed!). And this:

produces a more concise result set with no unnecessary duplicated columns.

... which I think suggests you're using select *, otherwise you would just select one of the values, albeit with a couple of extra characters for the qualifier. Using select * is generally considered bad practice (here for example) for anything other than ad hoc queries and some subqueries.

unable to group the data in ORACLE with GROUP BY clause

While using the USING clause in a query, specifying table name is redundant. Only the column name is enough information for the database. For example - select office_id. Try this :

select office_id,
count(staff_id)
from offices join staffs using (office_id) join managers using (manager_id)
group by office_id;

Refer to this similar question : Cannot have a qualifier in the select list while performing a JOIN w/ USING keyword

distinct() function (not select qualifier) in postgres

(The question is old, but comes high in Google results for “sql distinct is not a function” (second, first of Stack Overflow) and yet is still missing a satisfying answer, so...)

Actually this is the ordinary DISTINCT qualifier on a SELECT -- but with a misleading syntax (you are right about that point).

DISTINCT is never a function, always a keyword. Here it is used (wrongly) as if it were a function, but

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

is in fact equivalent to all the following forms:

-- add a space after distinct:

select distinct (pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

-- remove parentheses around column name:

select distinct pattern as pattern, style, ... etc ...
from styleview
where ... etc ...

-- indent clauses contents:

select distinct
pattern as pattern, style, ... etc ...
from
styleview
where
... etc ...

-- remove redundant alias identical to column name:

select distinct
pattern, style, ... etc ...
from
styleview
where
... etc ...

Complementary reading:

  • http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
  • https://stackoverflow.com/a/1164529

Note: OMG Ponies in an answer to the present question mentioned the DISTINCT ON extension featured by PostgreSQL.

But (as Jay rightly remarked in a comment) it is not what is used here, because the query (and the results) would have been different, e.g.:

select distinct on(pattern) pattern, style, ... etc ...
from styleview
where ... etc ...
order by pattern, ... etc ...

equivalent to:

select  distinct on (pattern)
pattern, style, ... etc ...
from
styleview
where
... etc ...
order by
pattern, ... etc ...

Complementary reading:

  • http://www.noelherrick.com/blog/postgres-distinct-on

Note: Lukas Eder in an answer to the present question mentioned the syntax of using the DISTINCT keyword inside an aggregate function:

the COUNT(DISTINCT (foo, bar, ...)) syntax featured by HSQLDB

(or COUNT(DISTINCT foo, bar, ...) which works for MySQL too, but also for PostgreSQL, SQL Server, Oracle, and maybe others).

But (clearly enough) it is not what is used here.

Using Natural Join with Where operation

Part 1.

When you natural join, the columns that have been "naturally joined" lose their table aliases, so for example:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
(SELECT CardId FROM ...
) tmp
WHERE Contains.CardId = tmp.CardId

Here, both sides of the natural join share a column CardId, so you cannot refer to the table alias for this column, e.g.:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
(SELECT CardId FROM ...
) tmp
WHERE CardId = CardId

But obviously this makes no sense, since the natural join means that CardId = CardId by definition, so the above should be simply:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
(SELECT CardId FROM ...
) tmp

Part 2.

This natural join in the inner query:

SELECT CardId
FROM Costs
NATURAL JOIN
(SELECT Id FROM ...
) rc
WHERE Costs.CardId = rc.Id
AND ManaCardId IN (...)

Here, the two column lists (CardId) and (Id) have no columns in common, which means the natural join has nothing to join - which would normally result in a cartesian join. However, the where clause effectively does an inner join anyway because of Costs.CardId = rc.Id. So, just to make the code clearer, I would prefer to just use an inner join:

SELECT CardId
FROM Costs
JOIN
(SELECT Id FROM ...
) rc
WHERE Costs.CardId = rc.Id
AND ManaCardId IN (...)

Part 3.

Natural joins are generally frowned upon because they are dependent on which columns are selected - so if a developer adds a column to a select list but doesn't notice it's using a naturaly join, it may have unexpected side effects. It is generally good practice to join tables explicitly, e.g.:

SELECT Block FROM ( 
SELECT CardId, Block
FROM Contains
GROUP BY Block
UNION
SELECT CardId, Block
FROM Contains
JOIN
(SELECT CardId
FROM Costs
JOIN
(SELECT Id
FROM Card
WHERE RarityId IN (SELECT Id FROM Rarity WHERE RarityType='Legendary')
) rc
ON Costs.CardId = rc.Id
WHERE ManaCardId IN (SELECT Id FROM ManaCard WHERE ManaColor='Red')
) tmp
ON Contains.CardId = tmp.CardId
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

You can also simplify the innermost join:

SELECT Block FROM ( 
SELECT CardId, Block
FROM Contains
GROUP BY Block
UNION
SELECT CardId, Block
FROM Contains
JOIN
(SELECT CardId
FROM Costs
JOIN Card rc
ON Costs.CardId = rc.Id
WHERE Costs.ManaCardId IN (SELECT Id FROM ManaCard WHERE ManaColor='Red')
AND rc.RarityId IN (SELECT Id FROM Rarity WHERE RarityType='Legendary')
) tmp
ON Contains.CardId = tmp.CardId
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

Now, looking at this query I notice that you're UNIONing two queries on the Contains table - the second query being a subset of those rows. By definition, all the rows returned by the 2nd query are included in the 1st query, and UNION eliminates duplicates, so the above query is logically equivalent to:

SELECT Block FROM ( 
SELECT CardId, Block
FROM Contains
GROUP BY Block
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

I note that the query with the GROUP BY doesn't have any aggregates, so this will not work in Oracle. I think this query is equivalent to:

SELECT Block FROM ( 
SELECT DISTINCT Block
FROM Contains
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

Which counts the number of duplicate blocks from a query that returns a distinct set of Blocks! - which means this query is equivalent to:

SELECT DISTINCT Block FROM Contains;

I suspect there are some logical differences between the way that PHP runs this query vs. how it will work in Oracle - so the above simplification is probably wrong.

MySQL left join returning wrong number of results

I think the query is very close... but put your join condition on the types being the same too, and only put the QUALIFIER of the type in the WHERE clause associated with THIS MONTHs data

SELECT 
ktm.key AS keyword,
ktm.data AS this_month,
klm.data AS last_month
FROM
data AS ktm
LEFT JOIN data AS klm
ON klm.site_id = ktm.site_id
AND klm.key = ktm.key
AND klm.type = 'organic-keywords-last-month'
WHERE
ktm.site_id = 2
AND ktm.type = 'organic-keywords-this-month'
ORDER BY
this_month ASC

By applying the "AND ktm.type = 'organic...', that will properly qualify WHAT YOU WANT NOW.

However, the LEFT JOIN will also explicitly match based on "AND ktm.type = klm.type" to the clause. Notice I adjusted to similarly apply your "same site ID" as last month too.

I would ensure the data table has an index on (site_id, type, key)

Trying to Query 2 Access tables with 1 statement

Correct, an inner join would be the way to go. You basically need to construct a statement that joins the 2 tables by their common field. Something like this:

SELECT * FROM [AC_ECONOMIC] a
INNER JOIN [AC_PROPERTY] b
on [AC_ECONOMIC].SN=[AC_PROPERTY].SN
Where KEYWORD = '" + end + "'AND QUALIFIER = '" + qual + "' AND EXPRESSION LIKE 'SN%'

Above statement is just an example. I don't know if the common column between the 2 tables is actually called "SN".

UPDATE:

As suggested by @Geek, you can also alias the tables after you reference them in your statement, this saves you from typing the full table names every time. For example, the above statement can also be written as:

SELECT a.*, b.* FROM [AC_ECONOMIC] a
INNER JOIN [AC_PROPERTY] b
on a.SN=b.SN
Where a.KEYWORD = '" + end + "'AND a.QUALIFIER = '" + qual + "' AND b.EXPRESSION LIKE 'SN%'

Note how the WHERE clause is filtering using columns from both, "a" (AC_ECONOMIC) and b (AC_PROPERTY).



Related Topics



Leave a reply



Submit