Oracle as Keyword and Subqueries

Oracle AS keyword and subqueries

The pattern for the SQL 99 ANSI is that the table can have an alias WITHOUT the AS keyword so, you can take out AS and it should work on every RDBMS. See it on fiddle:

• MySQL
• Oracle
• PostgreSql
• SQLLite
• SQLServer

In ISO/IEC 9075-2:1999, section 7.6 <table reference>, page 232:

<table reference> ::=
   <table primary>
   | <joined table>

<table primary> ::=
   <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
   | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <lateral derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <collection derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <only spec>
      [ [ AS ] <correlation name>
         [ <left paren> <derived column list> <right paren> ] ]
   | <left paren> <joined table> <right paren>

Also confirmed to work:

• MS Access (Jet)

Select Statement Using Dual Table As A Sub Query In Oracle Generates From keyword not found Error

Your match_type column is generating the error. Oracle doesn't support relational operator matching. You may try below query -

SELECT a.dt_1,
       a.dt_2,
       CASE WHEN a.dt_1=a.dt_2 THEN 'TRUE' ELSE 'FLASE' END AS "match_type"
  FROM (SELECT TO_DATE(REPLACE('2020-05-14 00:00:00',' 00:00:00',''), 'yyyy/mm/dd') as "dt_1",
               TO_DATE('14/05/2020','dd/mm/yyyy') as "dt_2"
          FROM DUAL) a;

Can a subquery be used with the VALUES keyword?

You need to insert subqueries in parentheses. The opening paren for values doesn't count. It is the start of a list, not a subquery. You can include subqueries in the VALUES clause when they return one row and one column.

Instead, though, you can use this syntax:

insert into regions (region_id, region_name)
    select max(region_id) + 1, 'Great Britain'
    from regions;

Better yet would be to assign a sequence to the region_id (identity or auto-increment column in other databases) so it is assigned automatically. Then you would just do:

insert into regions (region_name)
    select 'Great Britain'
    from dual;

Missing Keyword Error - Join Subquery with Oracle

This portion does not make sense:

(SELECT CUST_NO, 
        MIN(CUST_CDE) AS CUSTOMER_CODE 
        FROM SCHEMA.TABLE2
        GROUP BY CUST_NO
) SCHEMA.TABLE2 T2 

It looks like you are giving two aliases to the subquery. But the first one isn't valid.

Your code could have other problems, but try removing the SCHEMA.TABLE2 after the ). I presume you want:

(SELECT CUST_NO, 
        MIN(CUST_CDE) AS CUSTOMER_CODE 
        FROM SCHEMA.TABLE2
        GROUP BY CUST_NO
) T2 

How does Subquery in select statement work in oracle

It's simple-

SELECT empname,
       empid,
       (SELECT COUNT (profileid)
          FROM profile
         WHERE profile.empid = employee.empid)
           AS number_of_profiles
  FROM employee;

It is even simpler when you use a table join like this:

  SELECT e.empname, e.empid, COUNT (p.profileid) AS number_of_profiles
    FROM employee e LEFT JOIN profile p ON e.empid = p.empid
GROUP BY e.empname, e.empid;

Explanation for the subquery:

Essentially, a subquery in a select gets a scalar value and passes it to the main query. A subquery in select is not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a count to the main query, which, as we know, would always be only a number- a scalar value. If a value is not found, the subquery returns null to the main query. Moreover, a subquery can access columns from the from clause of the main query, as shown in my query where employee.empid is passed from the outer query to the inner query.

Edit:

When you use a subquery in a select clause, Oracle essentially treats it as a left join (you can see this in the explain plan for your query), with the cardinality of the rows being just one on the right for every row in the left.

Explanation for the left join

A left join is very handy, especially when you want to replace the select subquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOIN keyword.

For more information read Oracle Docs on subqueries and left join or left outer join.

Two subqueries work fine individually but SQL command not properly ended when join by except [Explain like I'm five]

except is not a thing in Oracle; the equivalent keyword is minus: your query should just work if you change the keyword.

On the other hand, both queries are quite identical, so you could just merge the having clauses:

select p.productid, p.productname, p.productprice 
from product p
inner join soldvia s on p.productid = s.productid 
group by p.productid, p.productname, p.productprice 
having sum(s.noofitems) > 3 and count(s.tid) <= 1

Notes:

• always use standard, explicit joins (with the on keyword) rather than old-school, implicit joins (with a comma in the from clause): this old syntax should not be used in new code

• table aliases make the query easier to write and read

• in a multi-table query, always qualify all column names with the table they belong to, so the query is unambiguous and easier to understand

FROM keyword not found where expected - basic query

You'd need an alias on your inline view and on the * in your select list

select a.*, rownum 
  from (select 1 from dual) a;

Oracle SQL Query - Element containing every element in subquery

If I have this right, you want documents whose keywords contain all of Fred's keywords as a submultiset.

Setup (building on Sayan's example):

create or replace type number_tt as table of number;

create table documents(id, title) as
    select 1, 'Foo' from dual union all
    select 2, 'Bar' from dual union all
    select 3, 'Fred' from dual;

create table document_keywords(documentid, keywordid) as
    select 1, column_value from table(number_tt(1,2,3))   union all
    select 2, column_value from table(number_tt(1,2,3,4)) union all
    select 3, column_value from table(number_tt(1,3,5))

Query:

with document_keywords_agg(documentid, title, keywordlist, keywordids) as (
    select d.id, d.title
         , listagg(dk.keywordid, ', ') within group (order by dk.keywordid)
         , cast(collect(dk.keywordid) as number_tt)
    from   documents d
           join document_keywords dk on dk.documentid = d.id
    group by d.id, d.title
  )
select dk1.documentid, dk1.title, dk1.keywordlist
     , dk2.title as subset_title
     , dk2.keywordlist as subset_keywords
from   document_keywords_agg dk1
       join document_keywords_agg dk2
            on dk2.keywordids submultiset of dk1.keywordids
where  dk2.documentid <> dk1.documentid;

Results:

WITH abc AS( select
             FROM ...)
, XYZ AS(select
         From abc ....) /*This one uses "abc" multiple times*/
  Select 
  From XYZ....   /*using abc, XYZ multiple times*/