MySQL questions...

[milanuk]

Hello,

I am _extremely_ new to the whole SQL thing, and I have been looking around for some data on getting started. So far I have seen various posts on which features PostgreSQL or MySQL has, etc. and so on. At my stage, they probably won't affect me a whole lot, and the speed arguments won't be a big factor for me one way or the other, either.

The New Rider's Publishing book 'MySQL' seems to be highly rated for someone who has a basic understanding of *nix based systems, but needs some hand-holding when it comes to an intro to SQL and specifically MySQL. This pretty much describes my situation to a tee, so I will be picking up a copy at the first opportunity.

Until then, I've been looking at the MySQL tutorial a little bit, and I'm kinda unclear on a few items, specifically 'sub-SELECT' type statements:

1) Why doesn't MySQL have this SQL-92 standard (as far as I understand from browsing) feature? Obviously, it's not absolutely necessary, but it would seem that it would make life a whole hell of a lot easier.

2) What is the most common / most effective way of simulating / emulating / working around this feature?

3) Is this something that is 'in the works'? Any speculation on when this could be expected to show up in MySQL?

I was kind of torn as to which forum to post this in, since it is kind of an SQL language question, but also fits in the Web Serving or Software Topic area. And it would be more likely to get a wider range of people viewing it in the General Linux Questions forum, or one of the OT forums ;) If it needs to be moved, please let me know. If necessary, I may end up asking on the mysql mailing list when I get a chance.

Thanks in advance,

Monte

[Marcel2008]

I don't understand the problem.
SQL = SQL right? I've done some with progresql, and that comes with a great sql reference. MySQL should work with every sql standard statement.

Maybe some statements are depricated, but it should provide backward compatability.

Did this help ? :p

[milanuk]

Well, yes, SQL is SQL is SQL, but the issue is as far as I understand it, no one really implements a 'complete' version of the SQL standard. PostgreSQL is supposed to have the 'most' compliant implementation of the 'free' SQL software, but MySQL as I understand it, has traditionally been considerably less compliant but much 'lighter' and faster. Specifically, until the most recent version, MySQL hasn't supported transactions, something which is supposed to be important for 'rolling back' changes made to a database. Another thing that MySQL doesn't do is standard sub SELECTs i.e. SELECT WHERE (). Please forgive if my terminology is incorrect, as I said, I'm pretty green to this SQL thing, and this is just what I've picked up browsing around.

Monte

[milanuk]

Ok, ok. I found _most_ of the answer I was lookin for... I was browsing thru the MySQL manual and came across a different section than where I had read about the sub-selects before. Here it is, for any interested parties:
http://www.mysql.com/doc/M/i/Missing_Sub-selects.html

Still doesn't really explain _why_ MySQL doesn't support sub-selects... you would think that it would be a 'natural' thing for them to implement?

Monte

[Sweede]

MySQL is not no where as mature as some of the other SQL databases out there. Why? because its a fairly new database system.

if you read the Todo lists, they have plans to become fully SQL-92 complient in future versions. Sub-selects and full transaction support, foreign keys are next in line.

Just so you know, there is NO database that is more than 60-70% SQL-92 complient, MySQL is more standards complient than Oracle.

MySQL is designed for web based applications becuase its extreamly fast and very lightweight.

PHP and MySQL were designed to operate together. if your going to use MySQL in a webserver environment, you should be able to figure a work around for anything by reading the PHP manual. you should be able to figure it out in 10 seconds.

[Salmon]

Originally posted by Sweede:
PHP and MySQL were designed to operate together.

This is not really accurate. PHP and MySQL are very often used in conjunction with each other, but neither one was really developed specifically with the other in mind. PHP has support for a whole host of other database systems, and MySQL is just one.