Silly beginners question about joins

Question

I have no previous experience of SQL but I just now have an assignment where I'm supposed to join 2 tables using different kinds of joins (on physical paper, not in an editor).

Table 1 is called Cities and has 2 columns, Cities and Countries. Table 2 is called Countries and has 2 columns, Countries and Currency.

The key is Cities.Countries > Countries.Countries

So, the joins are easy enough to understand, my question is more about the order in the output table and what columns are shown (been watching tons of tutorials but I can't grip these two details).

This is the first command:

SELECT Cities.*, Countries.* 
FROM Cities 
INNER JOIN Countries ON Cities.Countries = Countries.Countries

I'm wondering two things,

1. Does the SELECT Cities.*, Countries.* part mean that the output will show ALL 4 columns and thus 2 columns with identical values, or will KeyA and KeyB be merged into only one column?

2. If I got several "Italy" for example in the Countries column of the Cities table (different cities), but the are not right after each other in the table, will the output order them so that all Italy values come adjacent or just go with the order from the original table?

Very basic questions I'm sure but I don't have access to a simulator of any kind! :)

Thanks in advance!

Answer 1

1. Yes. As mentioned in the comments, you should try it out yourself.
2. Neither one.

It is very important that you learn this and drill it into your head

databases do not store things in order.

If you want things out in a particular order, you must specify the ORDER BY clause.

There is no such thing as "first row" until you give an ORDER BY clause.

Answer 2

To clarify Michael's answer a little bit and add additional information:

1. The * keyword is syntactical shorthand that means all columns. So when you say SELECT Cities.*, Countries.* you're instructing the database to return all columns from the Cities table, followed by all columns from the Countries table. That is, all 4 columns will be returned, including the Countries column being part of the result set twice.

   Additionally, you can convert your shorthand SELECT statement into an even shorter one by literally writing SELECT * instead. Though best practice is to explicitly list out only the columns you want returned and not use the shortcut of * (except for adhoc throwaway queries or very rare circumstances). Without going into too many details, it can affect performance and / or hurt maintainability of your code, if you cheat by using the * keyword.

   So you could actually specifically list the columns like so SELECT Cities.Cities, Cities.Countries, Countries.Currency. Take notice that by explicitly listing the column names, I don't have to tell the database to redundantly return the Countries column twice.

   Also, it doesn't matter which table you ask for the Countries column from in this case, since that's your key field being used in an INNER JOIN between the two tables (the value will always be the same in either Countries column). I chose to ask for it from the Cities table in my example but it would be no different of an output if you asked for it from the Countries table like so SELECT Cities.Cities, Countries.Countries, Countries.Currency instead.

2. There is no guarantee on the order of your results from a query unless you specifically use an ORDER BY clause. So it's possible the first time you run your query, you get one ordering, and the second time you run it you get a different ordering of results, unless you add the ORDER BY clause.

   That being said, to clarify something Michael said a little, is a table by itself is not known to store things in a particular order. And most importantly, a result set from a query has no guarantee on the order in which the results are returned (as mentioned in my previous paragraph), but databases can store things in a particular order.

   More specifically, when an index is created, it's persisting the data in an order defined by the fields in the index key, logically. Typically this is done with a data structure such as a B-Tree. The key difference here is that the data is logically stored in a particular order but that doesn't guarantee that data is returned by a query in any particular order. My previous paragraph still holds true, that even when an index is defined (for example on a table), and the data is logically sorted, there is no guarantee that the data will be returned in any particular order when queried - unless an ORDER BY clause is used.