77

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

This is my Select:

SELECT pCliente,
       'xxx.x.xxx.xx' AS Servidor,
       xxxx AS Extension,
       xxxx AS Grupo,
       xxxx AS Puerto
FROM DUAL;
Omer
  • 8,194
  • 13
  • 74
  • 92
wabregoc
  • 1,064
  • 2
  • 12
  • 17

6 Answers6

81

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
Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
  • 6
    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, https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388#19020485993865 – Shannon Severance Jul 31 '15 at 21:17
  • 1
    Thank you, this also works in Virtuoso Open-Source DB. – nuoritoveri Dec 19 '16 at 15:05
  • 2
    `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. – Andy Sep 06 '18 at 05:33
  • 1
    How about when I want to use [JOIN clause](https://www.w3schools.com/sql/sql_join.asp) 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 )` – AndyDaSilva52 Jan 29 '21 at 18:38
  • 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. – Lucio Menci Jan 28 '22 at 15:00
35

You don't need DUAL in MSSQLserver

in oracle

select 'sample' from dual

is equal to

SELECT 'sample'

in sql server

Omer
  • 8,194
  • 13
  • 74
  • 92
6

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)
Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1

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

pix
  • 1,264
  • 19
  • 32
HENG Vongkol
  • 883
  • 1
  • 8
  • 10
  • 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. – TheRealZing Feb 19 '21 at 00:42
0

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
Ersel Er
  • 731
  • 6
  • 22
Chakradhar
  • 11
  • 1
0

It's much simpler than that. Use literal values to establish data types. Put quotes around column names if they need special characters. Skip the WHERE clause if you need 1 row of data:

SELECT  'XCode' AS XCode
       ,1 AS XID  
       ,'XName' AS "X Name"
       ,'YCode' AS YCode
       ,getDate() AS YID
       ,'YName' AS "Your Name"
 WHERE 1 = 0
T.S.
  • 18,195
  • 11
  • 58
  • 78
eBear
  • 1
  • 1