Why cannot I select from more than one table in Oracle?

Question

Hi,

I'm using Oracle 10g Express Edition 10.2 and I use it from a C# application with Oracle.DataAccess 2.111 assembly.

I can select data from one table, but if I try to select data from several tables, the set is empty.

select * from Table1

works well, but:

select * from Table1, Table2
select * from Table1, Table2 where Table1.Id = Table2.Id
select * from Table1 inner join Table2 on Table1.Id = Table2.Id

will all three give no results. When used like this:

using (OracleCommand getData = new OracleCommand("select * from Table1, Table2", oracleConnection))
{
    using (OracleDataReader reader = getData.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine("Got record");
        }
    }
}

there will be no "Got record" (while Oracle SQL Developer shows thousands of records for the same query).

What's strange is that if I run the same three queries from Oracle SQL Developer 2.1, all are returning results.

What's happening?

---

Edit: I'm really stupid, I forgot to check if there is something in Table2. In fact, Table2 is currently empty. Doing select * from Table1 left join Table2 on Table2.Id = Table1.Id shows everything well.

So my question will be a little different: given Oracle syntax, what is the right behavior of three "buggy" queries? To display everything or to display an empty set? And I'm still wondering why Oracle SQL Developer and my application are not displaying the same result.

Answer 1

Are you certain you are running in/as a schema that has access to the tables? Perhaps in SQL Developer, you are running as one user and when running from elsewhere you are running as another. I would check out the public synonyms and the privileges on the table, as well as find out what user you are connecting as.

Answer 2

Given your edit, the three buggy queries are all doing inner joins (the first one is really buggy as it is doing a cartesian product), and thus shouldn't return anything if Table2 is empty.

Are you sure those are the exact queries you're using, and not just modified examples.