Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[SQL] Making basic SQL statements

Joined
Apr 24, 2013
Messages
1,678
Reaction score
1,116
Hi!

Since all the SQL tutorials here seem to be very much outdated. I wanted to write a tutorial on how to write basic SQL statements. SQL is a VERY complex and nasty language, there's so much more to know than I can cover in a simple tutorial, but I will try my best.

I will start with a tutorial about selecting data, but if you guys want me to make more for updating, deleting, creating, subqueries, counting, summing or whatever, tell me.

Let's say we own a website which has users and these users can all update their profile with a certain country. Our tables look like this:

user
idcountry_idname
11Example Name1
21Example Name2
32Example Name3
42Example Name4
5NULLExample Name5

country
idname
1Australia
2Germany
3China

The most basic select statement looks like this:

PHP:
SELECT columnname, columnname, columnname FROM tablename;

Letā€™s say we want to select the name and the id of all users, we would write it like this:

PHP:
SELECT id, name FROM user;

This will return the name and id of ALL the records. It will look like this:
idname
1Example Name1
2Example Name2
3Example Name3
4Example Name4
5Example Name5

But what if we only want to know the name and id from the users who live in Australia? We first need to look in our country table and we see that Australia has id 1. Now we have to add a ā€˜WHEREā€™ statement to our SELECT. The most basic SELECT WHERE statement looks like this:

PHP:
SELECT columnname, columname, columnname FROM tablename WHERE columname = 'value' OR columname = 'value' AND columnname = 'value';

As you can see, you can add ORs and ANDs to your statement, this works just like you would expect it to work. If you are a programmer, you can see OR as || and AND as &&.

Now letā€™s select our Australian users:
PHP:
SELECT id, name FROM user WHERE country_id = 1;

This will return only Australian users. It will look like this:
idname
1Example Name1
2Example Name2

But what if we want to select id, name and country name? How do we do that? We can do that with a JOIN. There are a lot of different joins; INNER JOIN, OUTER JOIN, RIGHT JOIN, LEFT JOIN, RIGHT OUTER JOIN, etc.

This is a lot of information to take in at once, so weā€™ll keep it simple and narrow this guide down to two JOINs: the LEFT JOIN and the INNER JOIN.
So what is the difference? Iā€™ll show you this in the following examples, since writing it down is difficult.

The most basic JOIN looks like this:
PHP:
SELECT tablename1.columnname , tablename1.columname, tablename2.columname FROM tablename1
JOIN tablename2 ON tablename2.keycolumn1 = tablename1.keycolumn2;

The key columns represent a column belonging to table2 which is also present in table1.
Now to ALSO select the country name for all users, we write our SELECT statement like this:

PHP:
SELECT user.id, user.name, country.name FROM user
LEFT JOIN country ON country.id = user.country_id;

We can add some aliases to make our query a bit more readable:

PHP:
SELECT u.id, u.name, c.name FROM user u
LEFT JOIN country c ON c.id = u.country_id;

Using aliases is NOT a must, it just comes down to a matter of preference whether you want to use it or not.

However if you are selecting two columns with the same column name, in our example this is u.name and c.name, you have to add an alias to one of them. I would do it like this:

PHP:
SELECT u.id, u.name, c.name country_name FROM user u
LEFT JOIN country c ON c.id = u.country_id;

This query will return the following:
idnamecountry_name
1Example Name1Australia
2Example Name2Australia
3Example Name3Germany
4Example Name4Germany
5Example Name5NULL

But wait.. Example Name5 hasnā€™t got a country_id filled in, what if we only want users who do have a country_id? This is where the INNER JOIN comes in handy, it will remove all those NULL records from the query result.

We use the exact same query and only change LEFT JOIN to INNER JOIN, which will look like:
PHP:
SELECT u.id, u.name, c.name country_name FROM user u
INNER JOIN country c ON c.id = u.country_id;

Now our result is:
idnamecountry_name
1Example Name1Australia
2Example Name2Australia
3Example Name3Germany
4Example Name4Germany
 

Ben

Developer - JS
Developer
Joined
Jul 6, 2013
Messages
1,224
Reaction score
506
Oh ty ty ty Gaby :/ i got C# exam tomorrow and needed some revision on SQL because I need to database connections and my sql knowledge sucks balls...

This comes at a great moment.
 
Experienced Elementalist
Joined
Apr 4, 2015
Messages
237
Reaction score
88
Thanks, already know this, but this will help alot of people! Good explained, and it is easy to understand. MentaL this need a sticky.
 
Joined
Feb 22, 2012
Messages
2,100
Reaction score
1,271
Great job writing this Gaby! I've been soooo long with basic MySQL usage/understading, that I [STRIKE]basically[/strike] have never tried to improve it. Perhaps by lazyness... or lack of capacity.

In all cases, this is a great explaination of how JOINs work :):
 
ā—(ā°ā–æā°)ā—œSmileā— (ā°ā–æā°)ā—œ
Developer
Joined
May 29, 2007
Messages
2,167
Reaction score
898
How can SQL be out of date, if has practically been the same for years? I haven't seen a lot of change the past years tough.
 
Joined
Apr 24, 2013
Messages
1,678
Reaction score
1,116
How can SQL be out of date, if has practically been the same for years? I haven't seen a lot of change the past years tough.
It's amazing how people will always try to find something in a thread to bash on. I said the tutorials were outdated, not the language. If the last tutorial on SQL is from 2012, that's outdated in my opinion.
 
ā—(ā°ā–æā°)ā—œSmileā— (ā°ā–æā°)ā—œ
Developer
Joined
May 29, 2007
Messages
2,167
Reaction score
898
It's amazing how people will always try to find something in a thread to bash on. I said the tutorials were outdated, not the language. If the last tutorial on SQL is from 2012, that's outdated in my opinion.
Asking a question is not the same as bashing. Ah, I see thanks for posting it! :D
 
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
Select and join aren't knew features in SQL, but that doesn't mean this tutorial is less helpful than existing ones. The replies are evidence that show there is an audience for this tutorial and it has helped people, so good job Gaby. The rules encourage improving existing tutorials over creating new ones, and grave-digging isn't a bad thing in the tutorial's section. But again, this tutorial clearly helped people, and the content is good, so it must be good that you posted it.
 
Back
Top