What is the equivalent of the Oracle "Dual" table in MS SqlServer?

sql-server oracle dual-table

This is my Select:

SELECT pCliente,
       'xxx.x.xxx.xx' AS Servidor,
       xxxx AS Extension,
       xxxx AS Grupo,
       xxxx AS Puerto
FROM DUAL;

Jean-François Savard

In sql-server, there is no dual you can simply do

SELECT pCliente,
       'xxx.x.xxx.xx' AS Servidor,
        xxxx AS Extension,
        xxxx AS Grupo,
        xxxx AS Puerto

However, if your problem is because you transfered some code from Oracle which reference to dual you can re-create the table :

CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

It would be best to change the queries, instead of introducing a dual table in SQL Server. Oracle's optimizer recognizes the dual table as special and skips accessing it, asktom.oracle.com/pls/asktom/…

Thank you, this also works in Virtuoso Open-Source DB.

dual was originally created for some internal use case where joining to it would duplicate rows, but at some point things got out of hand and it became common to select from it because Oracle stupidly requires a FROM clause even if the value being selected doesn't require a table.

How about when I want to use JOIN clause like at this example

SELECT * FROM ( SELECT 'COL1' COL1, 'COL2' COL2, M FROM DUAL FULL OUTER JOIN (SELECT 'COL3-ROW1' M FROM DUAL UNION ALL SELECT 'COL3-ROW2' M FROM DUAL) ON 1=1 )

I had a simil issue. I had to write a particular query in a framework that write query by itself. I used the table (values('x')) DUAL(DUMMY) to retrieve the result from a query that has a single column with an ANY result (in bold what writes the framework, in italic what I put as parameter): SELECT CASE WHEN 'X' = ANY (SELECT COLUMN FROM TABLE) THEN 1 ELSE 0 END RESULT FROM (VALUES('X')) DUAL(DUMMY) . It works well in most SQL engines.

Omer

You do not need DUAL in mssql server

in oracle

select 'sample' from dual

is equal to

SELECT 'sample'

in sql server

I see no reason to answer the same one year later after accepted answer...

Same answer but clearer. In saying that, I ran both examples without a FROM and got an error.

Community

While you usually don't need a DUAL table in SQL Server as explained by Jean-François Savard, I have needed to emulate DUAL for syntactic reasons in the past. Here are three options:

Create a DUAL table or view

-- A table
SELECT 'X' AS DUMMY INTO DUAL;

-- A view
CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;

Once created, you can use it just as in Oracle.

Use a common table expression or a derived table

If you just need DUAL for the scope of a single query, this might do as well:

-- Common table expression
WITH DUAL(DUMMY) AS (SELECT 'X')
SELECT * FROM DUAL

-- Derived table
SELECT *
FROM (
  SELECT 'X'
) DUAL(DUMMY)

pix

In SQL Server there is no dual table. If you want to put a WHERE clause, you can simple put it directly like this:

SELECT 123 WHERE 1<2

I think in MySQL and Oracle they need a FROM clause to use a WHERE clause.

SELECT 123 FROM DUAL WHERE 1<2

Not sure why people didn't like Heng's answer - Heng expanded the previous answers in that Heng pointed out that you can use a where clause without the from as well, which the other solutions didn't mention.

Ersel Er

This could be of some help I guess, when you need to join some tables based on local variables and get the information from those tables:

Note: Local variables must have been

Select  @XCode     as 'XCode '
       ,@XID       as 'XID   '  
       ,x.XName    as 'XName '
       ,@YCode     as 'YCode '
       ,@YID       as 'YID   '
       ,y.YName    as 'YName '
From (Select 1 as tst) t
Inner join Xtab x on x.XID = @XID
Inner join Ytab y on y.YID = @YID

What is the equivalent of the Oracle "Dual" table in MS SqlServer?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US