Select Without a from Clause in Oracle

Select without a FROM clause in Oracle

No, in Oracle there is no SELECT without FROM.

Using the dual table is a good practice.

dual is an in-memory table. If you don't select DUMMY from it, it uses a special access path (FAST DUAL) which requires no I/O.

Once upon a time, dual had two records (hence the name) and was intended to serve as a dummy recordset to duplicate records being joined with.

Now it has but one record, but you can still generate an arbitrary number of rows with it:

SELECT  level
FROM    dual
CONNECT BY
        level <= 100

MySQL also supports dual (as well as the fromless syntax).

Equivalent of SELECT without FROM in Oracle

In Oracle, the SELECT statement must have a FROM clause. However, some queries don’t require any table like the example you provided. You can use the DUAL table which is a special table that belongs to the schema of the user SYS but it is accessible to all users.

The DUAL table has one column named DUMMY whose data type is VARCHAR2() and contains one row with a value X.:

Select -1 AS DeptSK, 0 AS DeptID, "Undefined" AS DeptName
FROM dual

Oracle SQL select rows that are not in GROUP BY clause

You can use analytical functions:

SELECT first_name, second_name, score, COUNT(*)  over (partition by score) FROM tab ;

Oracle missing FROM clause

Yes, that's exactly what dual is supposed to be used for.

Selecting from the DUAL table is useful for computing a constant
expression with the SELECT statement. Because DUAL has only one row,
the constant is returned only once.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm

Sql syntax: select without from clause as subquery in select (subselect)

This is the default behavior for the SQL language and it is defined on the SQL ANSI 2011 over ISO/IEC 9075-1:2011(en) documentation. Unfortunately it is not open. This behavior is described on the section 4.11 SQL-Statements.

This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:

select id, (select name) from some 

It will try to find that name field as a column of the outer queries to process.

Fortunately I remember that some while ago I've answered someone here and find a valid available link to an SQL ANSI document that is online in FULL but it is for the SQL ANSI 99 and the section may not be the same one as the new document. I think, did not check, that it is around the section 4.30. Take a look. And I really recommend the reading (I did that back in the day).

Database Language SQL - ISO/IEC 9075-2:1999 (E)

Oracle - How to use & without being asked about the value?

Execute

SQL> set define off

before running your code.

---

SQL> select '&test' from dual;

'&TES
-----
&test

SQL>

---

If you want to "declare" it, then use var:

SQL> var test varchar2(200);
SQL> exec :test := 'some value';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
some value

SQL>

---

In dynamic SQL: I won't lock anyone, but - I'll change my password.

SQL> connect scott/tiger
Connected.
SQL> var test varchar2(200);
SQL> exec :test := 'lion';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
lion

SQL> begin
  2    execute immediate 'alter user scott identified by ' || :test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect scott/lion
Connected.
SQL>

Can in line views in oracle sql contain not in clause in the query?

Oracle calls subqueries in the FROM clause "inline views".

These are generic SELECT queries. They can contain NOT IN with subqueries. The problem with your query is a lack of ON clause and the use of double quotes for a string constant:

select *
from t1 inner join
     (select *
      from t2
      where t2.id not in (select ID from t2 where city = 'Paris')
---------------------------------------------------------^ single quotes
     ) t2
     on t1.? = t2.?
-----^ on clause

Note: I would discourage you from using NOT IN with subqueries, because they do not work as expected if any returned values are NULL. (If that is the case, then no rows are returned.)

I advise using NOT EXISTS instead.

---

Related Topics