In Clutch Team, Tech Hiring, Thoughts

 

(Source: XKCD)

In the technical recruitment world, we deal with a lot of data. A lot of it is pretty sensitive. From internal client processes and metrics to candidates’ desired salaries and interests, we’re entrusted with a great deal of information from a huge number of sources – information that then needs to be evaluated and considered within the larger context of trends in the tech industry.

 

On a seemly monthly basis, software engineers are inundated with new languages, frameworks, and methodologies to keep up with, or at least maintain a cursory awareness of. As recruiters, we do the same. But when it comes to dealing with data, sometimes it’s best to go back to the basics. Good ol SQL!

First appearing in 1974, SQL was one of the very first relational data languages, and is now also the most widely used. I recently took an Introduction to SQL workshop through the Practical Programming Institute to learn more about this foundational database language, and how it can be used to optimize technical recruitment practices as well as other ways it might make information management more efficient and secure.

The primary tool we used to get started with writing SQL queries was w3school’s Try-SQL Editor, and used it to explore the SELECT clause in a query. SELECT is a powerful tool for reading, querying, and organizing specific information in a database table.

First a quick overview of aggregate functions, which function much like excel functions. They are used within SELECT queries.

  • DISTINCT (number of unique values in a row/column)
  • COUNT (number of items in a row/column)
  • SUM (total value of items in a row/column)
  • MAX (highest value item in a row/column)
  • AVG (average value of items in row/column)

The key to understanding SELECT is understanding its syntax tree, which consists of: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT -> ; Here’s what it all means:

  • FROM: what database do we what data from?
    • Ex: FROM CandidateDatabase;
  • WHERE: defines the data that we want to query out
    • Syntax: =,>,>=,<=,<,<>,LIKE ‘%’, IN, IS NULL, BETWEEN, AND, OR, NOT
    • Ex: WHERE CurrentCompany = ‘Google’;
  • GROUP BY:
    • Syntax: for each group, apply an aggregate function (ONLY aggregate functions)
      • SELECT must always match GROUP BY
      • GROUP BY will always come with an aggregate function
    • Ex: Select Candidates, COUNT(CandidateName)
      • FROM Google
      • GROUP BY Candidates
  • HAVING: defines the group data that we want to query out (similar to WHERE but only for groups)
    • Syntax:
      • Only to be used with GROUP BY statements
      • HAVING only goes with aggregate functions
    • Ex: SELECT GoogleCandidates, COUNT(CandidateName)
      • FROM Candidates
      • GROUP BY GoogleCandidates
      • HAVING COUNT(CandidateName) > #;
  • ORDER BY: how do we want to order the table?
    • Syntax: DESC, ASC
    • Ex: ORDER BY Date DESC;
  • LIMIT: how do we want to limit the number of results and cut off the results?
    • Ex: SELECT * FROM CandidateDatabase LIMIT 5;

A final, critical piece of syntax in SQL is the JOIN clause. It’s generally used within SELECT for dealing with data that is spread out between multiple tables. One table is the left table, and one is the right table.

  • Ex: Table 1 is a table of Candidates and Table 2 is a table Clients. How would you match Candidates to Clients?
  • There are four different types of joins (see visual below):
    • INNER JOIN – Will only give you rows where you have rows from the left AND the right table. It will drop rows where there is no match. (If something isn’t common to both tables, drop it)
      • Ex: Only show Candidates who have corresponding Clients.
    • LEFT OUTER JOIN – Always keep the rows in the left column. It’s okay to drop rows from the right column.
      • Ex: Only show Candidates, regardless of whether they have corresponding Clients.
    • RIGHT OUTER JOIN – Always keep the rows in the right column. It’s okay to drop rows from the left column.
      • Ex: Only show Clients, regardless of whether they have corresponding Candidates.
    • FULL OUTER JOIN – Keep both rows, don’t drop from any of the tables.
      • Ex: Show all data from both tables, matching Candidates and Clients where possible. Fill in with null wherever there isn’t a match in a row.

(Source: Mode Analytics)

While different flavors of SQL (MySQL, Postgres, Oracle…etc.) differ slightly in syntax, regardless of what version you use, it is a fantastically practical tool for understanding data organization / structure. With technical recruitment in particular, understanding SQL fundamentals will elevate your understanding of how your company’s CRM / ATS system works, and consequently, how best to serve your clients and candidates!

Recent Posts
Contact Us

What can we do for you?

Not readable? Change text. captcha txt

Start typing and press Enter to search