Re: Question about SELECT statement

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 457,853 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,313 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Question about SELECT statement

•

Join Date: Jun 2005

Posts: 71

Reputation: Kate Albany is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

Kate Albany Kate Albany is offline

Offline

Junior Poster in Training

Re: Question about SELECT statement

Oct 1st, 2005

Hello,

The reason that your query does not work correctly is because by using <> in this manner you are creating a 'partial' Cartesian Join between the two tables. In doing so the query returns you a 'partial' Cartesian Product. Though this fact is masked from you in your first example by the use of 'distinct'. Run your query using 'select *' and you will see what is really happening. Google for Cartesian Join/Cartesian Product to find out more.

The example below is in Oracle syntax. So I suspect you will have to translate the (+) syntax for use with MySQL. It is an inner or outer join, I can never remember which. I just know which side of the join to place the (+) when needed

Help with Code Tags

(Toggle Plain Text)

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set feedback on;
SQL> select * from users1;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> select * from users2;

USERNAME
--------------------------------------------------
user3
user4
user6

3 rows selected.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

SQL> delete from users2 u2 where u2.username = 'user3';

1 row deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user5

4 rows selected.

SQL> delete from users2;

2 rows deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> rollback;

Rollback complete.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

Hope that helps

Kate

DaniWeb Home > Forums > Web Development > Databases > MySQL > Question about SELECT...

Re: Question about SELECT statement

Question about SELECT statement

Re: Question about SELECT statement