Joins/Advanced SQL Concepts

LoganK

I'd like to learn some advanced SQL concepts so that A) I understand them here on the board (if you notice, I tend to stay away from the DB forum 🙂) and 😎 when the need arises, I can use them without hesitation.

Here's the question (it's an odd one): what type of data/database structure might be good for learning joins and more advanced SQL concepts? What I mean is this: right now, I have no databases that use joins, nor can I think of any that will allow me to experiment with joins effectively. So what types of databases would be good? Like: library (check in/out system, reference books in one table and checked out in another), company, etc. I'd obviously be putting in some fake data so I can try to learn more about them.

I hope I explained this right; if not, ask me to explain and I'll do my best!

sneakyimp

there are some pretty good examples here:

http://dev.mysql.com/doc/mysql/en/join.html

LoganK

Later on (in the comments) gave me some great tips. Thanks!

Anyone else have suggestions?

sneakyimp

I'm curious about this too. I still haven't been able to put my finger on the difference between this:

SELECT u.name, s.amount FROM user u, salary s WHERE u.id=s.userid

and this

SELECT u.name, s.amount FROM user ud
LEFT JOIN salary s
ON u.id=s.userid

LoganK

I agree - I always find myself using the first method (or some derivative thereof), and ask myself, "Would a JOIN be more appropriate? Is it better? Faster? More effecient? Help me MySQL gods!!!"

sneakyimp

Actually, on further reflection, i think the second approach will return ALL users with NULLS for folks who have no records in the salary table. the first approach will only return user records which have a connected salary record.

as for INNER, OUTER, LEFT, and RIGHT joins. that totally mystifies me.

Roger_Ramjet

I suppose it can seem strange at first, but once you understand Data Normalisation then most of it becomes completely obvious.

So, read up on normalisation and practice it.

Then set up some simple tables with normalised data, and not too much of it, and experiment to see what you get with each of the different join types.

That is the best way to learn: and you will learn an awfull lot from your mistakes. Some of the common errors can produve some amazing results that will really ram home the lesson.

I would suggest that you do this with something like Access that has a query designer. It is quick to use and you can view the generated sql to see what the correct syntax is for the various join types. Then try editing the code and see what effect it has on the results.

Drakla

There's a massive, massive difference between a left join and a 'comma' join. The left join will return every row from the table listed first (left) no matter what the condition in the ON clause, but will still be filtered by the WHERE clause. The ON part basically tells it what to include from the table joined to - and it will either be a match or NULL, hence LEFT JOINs are mostly used to search for non matching rows

SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL

That'll get you all id's from table 1 that don't exist in the other table. The right join is pretty much the same, but you just specify the tables in the other order.

I'm rubbish at explaining this because it's so natural to me now. I find the best time to teach something is just after you've learnt it and understood it, because you can easily relate to what it was like not knowing it.

sneakyimp

thanks guys. drakla explained my last post more coherently.

roger's recommendations resonate rightly too. 😉

i found this page which seems to address our questions almost directly:
http://www.databasejournal.com/sqletc/article.php/1428511

there are also some great pages in the google results:
http://www.google.com/search?hl=en&q=%2Bsql+%2Bnormalization+&btnG=Google+Search

LoganK

Thanks for all the posts! I'm sure all this info will come in handy. I've heard of database normalization but I've never really taken the time to delve that deep into it. I'll take Access into consideration - however, for MySQL, isn't the MySQL Query Browser a "query designer" as you call it?

I'll definitely be reading up on it during the next week or so. After that, I've got a previous commitment which will take up all of my time for two weeks, so I've gotta try and learn all this soon!

Roger_Ramjet

If you don't know and do 'normalisation' then you'll never produce a decent robust database. It's not a nicety, it is absolutely fundamental to relational databases. Done properly, full data normalisation leads naturally to the database table schema, and makes all subsequent work with it much easier.

Dunno about mysql query browser? If it lets you drab and drop table relations, columns etc, and generates the sql then great. If it has inbuilt wizards for generating the more complex query structures then even better. I did not mean that you had to use access, just something like it so you can learn about joins and the different results they produce.

LoganK

I do know some data normalization - just not enough to consider it full. BTW, are you english? You spell "normalize" with an "s".

MySQL query browser - I actually don't know. I d/l the tool when I first installed MySQL but haven't used in since 😃 But I'll stick with Access for the time being.

Roger_Ramjet

Yes I'm english, but I'm likely to spell words any old how even if I was not. English is OUR language by the way and ours are the correct spellings of ALL english words. Your funny american dictionaries and spellings are the wrong ones, no matter what you yanks think.

As to access, it uses a different dialect of sql from mysql, so don't expect to be able to use access queries in your code unless you modify them. What it will do it to let you focus on understanding joins without being distracted by writing the sql as well. Just concentrate on understanding the different results you get from different join types.

LoganK

English is your language? Now I thought that it was actually comprised of two sub-languages, "American English" and "[The] Queen's English" or "British [English]". Normalise. Dustbin. Mum. I don't understand any of your British terms!!! My "funny" American dictionary suits me just fine 😃 Also, if you were to check, what do you would think would be the majority of how they spell "normalize" - with an "s" or a "z". I tend to think that American English is more prevalent than British. A side note on British/American differences: I will concede that I like your accent. British accents sound a bit more refined (in some instances).

I'll start trying Access at some point today. I just gotta find a computer with Access on it! (My main computer has Access, but I only have access to computers without Access. Did that make any sense?)

toplay

Link # 2 in my signature line under "MySQL:" has pretty good examples. Their specific page on joins is at: http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml and uses music and artist table examples.

Link # 1 has a way for you to test stuff right online. Select their 4), 5), and 6) options about joins. You can practice real time on their site without you having to create your own tables.

hth.

Drakla

Ahhh Logan - I shall tell thee of the thing that shall stop much fannying about with ODBC connections in Windows and let you connect straight to an .mdb

function odbc_connect_mdb($filename, $user="", $password="")
{
	$connStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$filename";
	return odbc_connect($connStr, $user, $password);
}


$dbh3=odbc_connect_mdb("c:\databases\fpnwind.mdb") or die ("Couldn't connect THREE");
$query3="SELECT TOP 10 * FROM PRODUCTS";
$sth3=odbc_exec($dbh3, $query3) or die ("FAILURE 3");

That's well old, that code - hence no spaces around the equals signs, but it'll still help.

I also started with this for the Jet SQL reference http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_list.html

And think I might have paddled around a bit here http://www.mvps.org/access/

Access did actually have a lot of stuff two years ago that MySQL doesn't, like subqueries and crosstabs, but that's still no reason not to run screaming from it.

LoganK

Very nice links...where do you compile all these resources from? They're great!

Drakla

LoganK wrote:
Very nice links...where do you compile all these resources from? They're great!

I've actually got a mysql links database what I done wrote mysql, and in Firefox or IE you just add bookmarks to the 'Link Dump' folder and then press a button and it adds them to the database - they go into 'Uncategorised' then you can add categories and then check boxes next to them and say "Go to that category, you!" and then they're all displayed in a nice DHTML menu with categories that open and close like lovely things when you click their names.

It's handy for hiding sites that promote 'Gentlemenly Leisure' from the girlfriend, as her php skillz is wakk!

toplay

LoganK wrote:
Very nice links...where do you compile all these resources from? They're great!

I assume you're referring to me. Well, I just came across them over time and use the popular ones from all the questions people ask about. I'm a moderator at another PHP forum.

LoganK

Actually, Drakla, I was referring to toplay's collection of ammassed links...but not offense to you, your links are fine too! I like that function you provided me - quick question, do I have to pass the query the database connection handle? With MySQL, I only need to do that if I have more than one connection open. Other than that, it's great, and I'll check out those links. Access is still a far cry from being like the MySQL that I know and love - mainly, it's not free. But among other things, I like Access for the fact that I can program VB right into it - and the *.mdb file holds everything - executable code, database, the whole shebang. I'm confused about what you wrote about bookmarks in Firefox/IE and DHTML...I'm not going to ask about "Gentlemanly Liesures" 😃

Toplay - what PHP forum? I'd like to branch out and see some other forums that I can participate it.

Edit: Drakla, I'm catching up to you in post counts! 😃