I was recently working on a social network back-end when I came across a problem. There was a users table, and these users had to have the option of “friending” each other. And so I created a “connections” table, which associated user to user. There were a few extra pieces but essentially what I ended up with was a table that stated the ID, friendA and friendB. I did not want multiple records for each connection so for each “friend” link there would be only one connection record.

The problem here is when it came time to use code to display the list of a given user’s friends, I hit a bit of a wall. I didn’t think I could arrange the users in any sort of order because I would need to use two queries like this (assuming 1 is your ID as the user trying to query the database for a list of your friends):

		SELECT
		user_to_user.id,
		user_to_user.userA
		FROM
		user_to_user
		WHERE
		user_to_user.userB = '1'

		SELECT
		user_to_user.id,
		user_to_user.userB
		FROM
		user_to_user
		WHERE
		user_to_user.userA = '1'

The first query checks all results where you are the user initiating the “friend” connection, and the second checks results where others initiated the connection with you. I thought I would have to loop through the results with userA and then userB separately.

As it turns out, I was wrong. The UNION operator is used to combine the result-set of two or more SELECT statements. So all I had to do is put UNION between the two queries and finish up with an ORDER BY clause and I was in business. Incidentally, I’ll note that the results of this would have columns id and userA – but what about userB? W3Schools says it best: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION. So all of the results – both userA and userB – will return under the userA column. If I were to swap the queries so I ask for the userB results first, then all results would be as userB.