Difference between Make Query Table vs. Make Table View

Question

What's the difference between the Make Query Table vs. Make Table View GP tools?

I can see that they have slightly different parameters. But generally speaking, they look like they do the same thing. Are there different use cases for those two tools?

Thanks.

BB1234 · Answer

Other differences:

* Make Query Table: Data is not editable, spatial.
* Make Table View: Data is editable, non-spatial.

bixb0012 · Answer

The answer lies mostly in the intro paragraph for each tool.  Underlining is my emphasis.

From Make Table View (Data Management)—ArcGIS Pro | Documentation

Creates a table view from an input table or feature class....

From Make Query Table (Data Management)—ArcGIS Pro | Documentation

Applies an SQL query to a database, and the results are represented in either a layer or table view. The query can be used to join several tables or return a subset of fields or rows from the original data in the database.

Make Table View supports a broader range of data sources and is much more efficient because it is a one-to-one tool, i.e., one table per table view.  Make Query Table allows for joining tables, but that expanded functionality comes at the cost of fewer supported data sources and less performance for simple cases.

KimOllivier · Answer

To operate tools on data stored on disk you always have to create in memory definitions or bitmaps first.

MakeTableView is much simpler, works on one table and is more reliable. It can make a subset of records and a subset of fields, but sometimes other tools get confused eg Intersect overlays.

MakeQueryTable attempts to set up a relational database expression across several tables in the same database. It may work for sample sets, but never works for me with real sized datasets. After trying it for a while I avoid it at all costs. Often it just hangs or crashes after hours.

So what to do? If you are using Model Builder or are trying to do interactive selections you are stuck with it. Simplify and reduce first by exporting to a temporary featureclass or partition into smaller sets and repeat.

If you are using Python there are much better options. You can copy the data straight into Numpy or Pandas and run python functions. Note SQL expressions have to be all in one database. Break this by extracting the whole dataset use a SearchCursor() then use python set functions to do joins. This means you are not limited to one database. You can use python dictionaries that are very fast lookups and can be very large say 1 million records. I often use list comprehensions with a SearchCursor to extract the table. Then loop through another table in another database with the dictionary. Alternatively I use a huge list with an SQL IN clause to do the selection, make sure the field is indexed in the database and you will have your selection in milliseconds compared to hours with MakeQueryView.