''Example of Inner Join or Join:
'''Tables in RED must have the required data in the ON statement
'''and within the WHERE statement.
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a JOIN UserAddress b ON a.UserID=b.UserID JOIN UserEmail c ON a.UserID=c.UserID WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now if all tables: Users or UserAddress or UserEmail do not contain the same unique strUserID or the same unique UserEmail, then your query will fail and no records will be displayed. All tables must have the UserID. Because the table is joined on the UserID's, all tables must have that same UserID. If all tables have 4 records each of the same UserID, 4 records will be displayed.
'''Example of Outer Join (Outer joins are Left joins or Right joins. Left join is by default)
'''If you use Outer Join instead of Left or Right join, Left Join will be default.
'''You may see this sometimes as "Left Join" or "Left Outer Join" or "Outer Join" which
'''all mean the same. Only "Right Join" or "Right Outer Join" mean the same.
'''Left Join, Left Outer Join, or Outer Join Example:
'''Tables in RED must have the required data in the ON statement:
'''Tables in Green are optional:
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a LEFT JOIN UserAddress b ON a.UserID=b.UserID LEFT JOIN UserEmail c ON a.UserID=c.UserID WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now the only table that is required is the Users table. If there are no records in the users table, the other two tables won't display data either. If there is a record in the User table, then it relates a record to the UserAddress Table, if no records in the User Address Table, only the Users table will be displayed. If there is a record, both tables will be displayed. Now if both those tables have records that relate, then the third table is searched. If there are no records in UserEmail, then only Users and UserAddress will be displayed. If there are records in the UserEmail as well, all three tables will be displayed. Now keep in mind, that all tables being displayed have the same related data used in the ON statement which happens to be UserID. All tables have the same UserID for at least one record. This process is repeated for each record. With this type of join, Table1 will always have the same or more records than Table2, and Table2 will always have the same or less than Table1 and the same or more records than Table3. Ex: Records: 5 5 1, or 5 5 5, or 5 4 3, or 5 5 0, or 5 0 0.
'''Right Join or Right Outer Join
'''Tables in Red must have the required data in the ON statement:
'''Tables in Green are optional:
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a RIGHT JOIN UserAddress b ON a.UserID=b.UserID RIGHT JOIN UserEmail c ON a.UserID=c.UserID WHERE c.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now make sure you see that the required WHERE statement changed as it is now c.UserID, not a.UserID. Now the only table required here is the UserEmail table. If there are no records within the UserEmail table with the supplied UserID, then no records will be shown. This is the same process as the Left Join but reversed and different tables are required. This method is much more confusing as everything is backwards, but this may be handy one day.
'''Union statements work the same way as joins, just separated queries. This requires more resources than a standard join, so learn joins over unions.
"SELECT UserName, UserID FROM Users WHERE UserID=" & strUserID & " UNION SELECT Address1, Address2, City, State, Zip FROM UserAddress WHERE UserID=" & strUserID & " UNION SELECT UserEmail FROM UserEmail WHERE UserID=" & strUserID & ""
''' Now the benefit of this is the fact that there is NO relation required. You can have many differen't tables that aren't related. It's a biotch to separate the info in the way you need, but it grabs it all in one query. The way the database handles UNIONS is separates it into all different requests then throws all the information at you at once. It's essentially the same as querying the database three times.
'''IN statements are used with the WHERE
"SELECT UserName FROM Users WHERE UserID IN (SELECT UserID FROM UserEmail WHERE NOT UserEmail IS NULL)"
'''What this code does is grabs all UserNames from Users where users don't have email address. The UserID from table Users equals the UserID from table UserEmail. This IN statement comes in handy so frequently, so learn it very well.
''The above query is essentially the same as the below query, but IN statements are faster and require less resources:
"SELECT a.UserName FROM Users a INNER JOIN UserAddress b ON a.UserID=b.UserID WHERE NOT b.UserEmail IS NULL"
''Oh, and IN statements are less confusing... no?
